Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Why need aggregation for a dynamic bar-chart/ table

Hello,

I'm using a variable like vFormula='0'. I'm using it so that I can switch a bar chart and a table dynamically  between two count condition for example with a qlikswitch or a button where I can implement '0' or '1' from the variable. These two conditions for the bar chart/ table dimension are:

if(vFormel='0',count(distinct{$<WorkID={'1111'},Status_Order={'running','opened',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders),weekname([EndingDate]))

and the second condition:

if(vFormel='1',count(distinct{$<WorkID={'2222'},Status_Order={'running','closed',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders), weekname([EndingDate])

So that I can switch between them i need to put them as one function:

if(vFormel='0',count(distinct{$<WorkID={'1111'},Status_Order={'running','opened',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders),count(distinct{$<WorkID={'2222'},Status_Order={'running','closed',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders))>0, weekname([EndingDate]))

So whatever variable results I have at the moment 1 or 0 so it should return me that condition, which I had defined.

I know just don't understand why this very straightforward approach doesn't work out,  Its actually Like:

When '0' then count me condition no.1 otherwise no.2. and return values >0  and the Calendar week form according to the EndingDate of it. 

Apprently it only works out when I do a aggr function and one more If-Statement before all of that, and put one more date dimension at the end:

aggr(if(if(vFormel='0',count(distinct{$<WorkID={'1111'},Status_Order={'running','opened',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders),count(distinct{$<WorkID={'2222'},Status_Order={'running','closed',''},Systemstatus-={"*wwww*","*tttt*"}>}Orders))>0, weekname[EndingDate)]),EndingDate)

Can anybody please explain why the "simplier" straighforward one without agg() returns zero and doesn't work like this one? Why I need aggregation to make it work?

Also instead of EndingDate at the end I also tried directly with weekname[EndingDate)], and that also gives me zero returns. Seems like I need to take the "original "EndingDate" without weekname function. 

I hope someone has an answer to it. 

 

Best. 

 

 

 

 

2 Replies
Highlighted
Luminary
Luminary

The reason you need to use AGGR is because Qlik needs to know what the calculation is supposed to aggregate over. 

You can use If statements to switch between Dimensions without any AGGR. However, if you want to leverage a Measure, you need to define what dimensions it should evaluate over.

Blog: WhereClause   Twitter: @treysmithdev
Highlighted
Creator II
Creator II

Hello @treysmithdev ,

thanks for the quick reply. Somehow its still not very clear to me.

You said in case I want to "leverage" the dimension I need to aggregate. That would suppose my case is already a "leverage" case? So how is that defined? Can you explain more deeply whats happening there? Actually when I don't do this I get zero returns. 

In simple way to explain my misunderstanding here. Didn't I already fullfill the typical if -statement when that happened: 

if vFormula is 0, then count me the first condition, otherwise the second condition, and when I have results greater then 0 then give me the EndDate in Calendar week form. 

Isn't the calendar week is already the return I get? Why do I have to put again a EndDate aggregation

 

You also brought me to the realization, that I don't need this aggregation with the second if-statement for my measures , only for my dimensions.