Aggregate function and grouping

[infock12]

Hi all,

Your help is much appreciated. I have 4 expressions in a combo chart budget vs actuals.

I have grouped 3 expressions as a stack bar - this is Actual spend

I have put the Budget as a line bar

It works fine. The first 3 expressions measures different staff group, namely permanent, agency, and volunteers. I have given the example below for the first expression for the substantive staff group. I have the list boxes for Month, Site, Division, Directorate, Category and Unit.

The problem here is when I apply the filters, say for example, Division, it gives the correct budget and actuals in the stack and line. But when I apply the Staff Group Filters, say 'BHP - Agency' the bar does not change. The below expression is giving an error. Please could you let me know if the syntax is right.

=aggr(nodistinct if(sum({<Category={'BHP - Substantive', 'Indigo - Substantive', 'Tesco - Substantive', 'Admin - Substantive', 'Simple - Substantive'}>}Actuals)), Month, Site, Division, Directorate, Category, Unit)

Thanks,

karthik

[jyothish8807 · Accepted solution]

Try like this:

sum({<Category={'BHP - Substantive', 'Indigo - Substantive', 'Tesco - Substantive', 'Admin - Substantive', 'Simple - Substantive'}>}aggr(sum(Actuals), Month, Site, Division, Directorate, Category, Unit))

Regards

KC

[Anil_Babu_Samineni]

I am not sure i understand your intend

Try like below

Aggr(sum({<Category={'BHP - Substantive', 'Indigo - Substantive', 'Tesco - Substantive', 'Admin - Substantive', 'Simple - Substantive'}>} Actuals), Month, Site, Division, Directorate, Category, Unit)

OR

sum({<Category={'BHP - Substantive', 'Indigo - Substantive', 'Tesco - Substantive', 'Admin - Substantive', 'Simple - Substantive'}, Month, Site, Division, Directorate, Category, Unit>} Actuals)

OR

Aggr(sum({<Category={'BHP - Substantive', 'Indigo - Substantive', 'Tesco - Substantive', 'Admin - Substantive', 'Simple - Substantive'}, Month, Site, Division, Directorate, Category, Unit>} Actuals), Month, Site, Division, Directorate, Category, Unit)

[infock12]

That works brilliantly KC. Thank you very much!

[infock12]

Thanks Anil, that works amazing too. Many thanks!

[jyothish8807]

you are welcome