Bottom-up Summary based on unique list members

Quentin

Hello everyone,

 

I am aggregating data in a module with three dimensions + Time:

 

M2, which has M1 as a parent and corresponds to models

R1, which is a list of roles

E3, which is a list of employees and has E2 Team and E1 Group as parents.

 

I am mapping my list of employees to their Roles which is why I have E3 as a dimension but I need to aggregate from E2 level so E3 won't be used for reporting.

I have the raw data but what actually matters in this module is whether there is data for E2 Team in each role, so I would know for example that in Team 1 there are 3 employees with Role X that have data in Dashboard 1 from M2, I have a line item that writes 1 if there is data or 0 if there is none to count matches here.

 

I need to sum up the data per role for each combination for M2/M1 and E2/E1 and here is where my problems start: it should be summarized a bit differently than just a sum for each level, below is an example of my issue.

 

In Model 1 I have 20 dashboards, in Team 1 I have 5 employees.

I am checking Role X (R1) within Team 1 (E2) for Dashboard 1 (M2) I see that 3 employees have data in that dashboard.

I am checking Role X (R1) within Team 1 (E2) for Dashboard 2 (M2) I see that 2 employees have data in that dashboard.

I do that check for each dashboard here, at the M2 level it is fine but when I go up at the M1 level the sum is done the count gets summed to around 40 which of course isn't possible at the R1/E2/M1 level since Team 1 has 5 employees.

 

Is there a way to sum only unique employees here ? Or should I split the module, using a different approach ? The end goal is that when going from M2 to M1 level is have the sum of unique employees with that Role in the Model, at each E2 and E1 level, not summing every count here. The Summary set as Max didn't work here for me as the bottom level is E3 and everything is 1 for this line item.

 

Please let me know what I am doing wrong here and if you have tips to achieve that, that would be great !

 

Thank you.

 

Best regards,

 

Quentin

rob_marshall

@Quentin 

 

Not sure if this will help, but what if you don't use E3 (the Employees) and just use an Employee Flat list, so the module would be M2 by Employees Flat and another module E2 by Employee Flat.  That way, you will get a true count of employees.

 

Does that help?

 

Rob

Quentin

Good morning Rob,

 

If I understand your solution correctly I would need to create another list same as E3 but without hierarchy, then map it into a module to get the employee count for each E2 member, and in another one count how many employees from the flat list (mirroring E3) per item in M2 ?

I am having a hard time figuring out how I would then aggregate it correctly at the higher levels when adding the R1 roles, could you please let me know your thoughts on that ?

 

Thank you.

 

Best regards,

 

Quentin

kathleenseiler

Hi @Quentin,

 

Are you able to provide some screenshots of the current set up? it's hard for me to visualize what the issue is. I'm thinking something must be wrong with the way the count is working at the lower level. If that team only has 5 employees, and you have 3 in dashboard 1 and 2 in dashboard 2, at the M1 level it would add up to 5.

 

Thanks,

Kathleen

Quentin

Good afternoon @kathleenseiler,

 

Please see below my module, my hierarchies are the following, from bottom to top level:

 

Roles

User -> Team -> Group

Dashboard -> Model

 

Here you can see that at the user level I have either 0 or 1 as my formula is "If there is data for the user at the Role/User/Dashboard level (lowest) then 1 else 0" to count the user as an active user.

 

 

 

 

Moving on to the team level, in the Controlling team we have no issue again as there are x members using the Dashboard:

 

 

 

 

 

Then, when aggregating to the Role/Team/Model level things get messy as the summary counts the sum for each dashboard in the model, as you see below and we know for a fact that this is wrong as we don't have that many "Full Access" users.

 

 

 

 

 

Our goal is to count unique users in the Model, so if we count three users in Dashboard X they shouldn't be added again from Dashboard Y when rolling up to the model level. The aggregation method here is fine at the team/dashboard level but if we go higher than that things become wrong.

 

Do you have an idea on how this can be solved ?

 

Thank you very much.

 

Best regards,

 

Quentin