Ignore the current selection within aggr() function

[#1]

Date	ClientName	Income	Cost
Sep-16	A	5	1
Sep-16	B	7	2
Sep-16	C	15	4
Sep-16	D	9	5
Sep-16	E	9	7
Aug-16	A	6	1
Aug-16	B	9	3
Aug-16	C	8	4
Aug-16	D	15	5
Aug-16	F	8	2

Above is a made up flat table to illustrate my question. I use QlikSense and my current selection is "Sep-16".

So, I want to divide my ClientName into 2 groups: Group that Income <=10 and Income >10. And I want to calculate the sum of all cost of each of these 2 groups for both Sep-16 and Aug-16. So I come up with something like this:

          For Sep-16: sum({<ClientName={"=aggr(sum({<Date={'Sep-16'}>}Income),ClientName)<=10"},Date={'Sep-16'}>} Cost)

          For Aug-16: sum({<ClientName={"=aggr(sum({<Date={'Aug-16'}>}Income),ClientName)<=10"},Date={'Aug-16'}>} Cost)

The formula works fine for Sep-16. However, Aug-16 formula doesn't work. What I think to be the problem, (95% sure about this), is that when Qlik create the aggr() data, it uses the ClientName exist within the current selection - "Sep-16" which are "A, B, C, D E" and ignore F, since there is no Sep-16 data for F.

And then, using the dimension of A B C D E, it then searches for and group clients income of <=10 in Aug-16. In this case, A B C fits the criteria. With these 3 clients, it sums the cost of Aug-16, (=1 + 3 + 4). Because of this, Qlik ignores F, which also have income <= 10, while I want F to be included as well.

I say I'm 95% sure about the above process is because when I switch the selection from Sep-16 to Aug-16, Aug-16 formula works fine, while Sep-16 formula excludes E.

Basically, it's a long-winded way of asking how I can make aggr() consider all ClientNames and ignore the current selection. Or is there a better way of achieving the same thing? Please correct me if i'm wrong anywhere or let me know if you need any clarification.

Thanks,

Tom

[petter · Accepted solution]

You shouldn't need to use Aggr-function to achieve what you want.

Try this expression instead:

Sum(DISTINCT {1<ClientName={"=Sum({<Date={'Sep-16'}>} Income)<=10"},Date={'Sep-16'}>} Cost)

and

Sum(DISTINCT {1<ClientName={"=Sum({<Date={'Aug-16'}>} Income)<=10"},Date={'Aug-16'}>} Cost)

Notice the DISTINCT and the set identifier 1 (which denotes ALL and not only the currently selected).

[#3]

Thank you so much! It works when I remove the whole aggr() function. Doesn't need the DISTINCT or 1 as well.

[StacyCui]

Hi, I met a little same question, But, for my dimension, I need re-define a new dimension based on measure. So i have to use aggr function, so it cause my dimension not the actual all the customers.

Here is my dimension, I want to divide customer in to 3 types according to OR Type which is CY Till OR- PY Till OR. It only show the customers within month selected. How to shows all the customer?

Dimension : aggr(if($(OR_Type)>0 ,'With OR Growth',
if($(OR_Type)<0 or $(OR_Type)=0 and $(V_CY Till_OR) <>0 ,'With OR Decrease',
if($(V_CY Till_OR)=0 ,'CY Order=0'))), customerID)

Measure : count(distinct {1}customerID)