topic Aggregate function and grouping in QlikView

Aggregate function and grouping

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregate function and grouping

jyothish8807 — Thu, 03 Nov 2016 16:48:38 GMT

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

Re: Aggregate function and grouping

Anil_Babu_Samineni — Thu, 03 Nov 2016 17:18:32 GMT

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)

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

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)

Re: Aggregate function and grouping

infock12 — Thu, 03 Nov 2016 17:50:25 GMT

That works brilliantly KC. Thank you very much!

Re: Aggregate function and grouping

infock12 — Thu, 03 Nov 2016 17:51:28 GMT

Thanks Anil, that works amazing too. Many thanks!

Re: Aggregate function and grouping

jyothish8807 — Thu, 03 Nov 2016 18:01:39 GMT

you are welcome