Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone. I have a QlikView problem that I've been trying to solve with set analysis. Would anyone be able to assist?
I have a pivot table with LOCATION and DAY as dimensions, and sum(FIELD_A) as the expression. Data as below:
LOCATION | MON | TUE | WED |
A | 1 | 1 | 1 |
A | 1 | 0 | 0 |
A | 1 | 0 | 1 |
B | 1 | 1 | 0 |
B | 0 | 0 | 0 |
C | 1 | 1 | 0 |
I wanted to get the count of distinct locations where sum(FIELD_A)>0, and I've been able to do this using:
count({<LOCATION={'=sum(FIELD_A)>0'}>} distinct LOCATION)
However this value is across all possible DAY values.
The part that I'm stuck on is getting the count of distinct locations where sum(FIELD_A)>0 for one particular day.
Ideally I'd just like to have a formula for e.g. DAY=TUE to put this value in a text box. Does anyone have any ideas? Thanks.
For DAY = TUE in a text box, why don't you try this?
Count({<LOCATION={"=Sum({<DAY = {'TUE'}>}FIELD_A) > 0"}, DAY = {'TUE'}>} DISTINCT LOCATION)
I don't think you will be able to evaluate the condition in a day basis using simple set analysis. The set expression is applied to the entire data model and not on a row by row (day by day) basis. You could do it with a Sum(If()) expression or by setting a flag in the load script.
Thanks, that's helpful - do you have any advice on how I might solve the problem with sum(if()) ?
For DAY = TUE in a text box, why don't you try this?
Count({<LOCATION={"=Sum({<DAY = {'TUE'}>}FIELD_A) > 0"}, DAY = {'TUE'}>} DISTINCT LOCATION)
Thanks Sunny, that's it!
I had tried something similar, but I used single speech marks ' instead of double "
Hi,
You can use Aggr function. Some thing like
Count( Distinct if(Aggr(Sum(Field_A),Day,Location) >0 ,Location)).
Thanks Muzammil. I've already started development using Sunny's method above, but it's good to know that Aggr is an alternative.