Set Analysis problem

[ben_pugh]

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.

[jonathandienst]

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.

[ben_pugh]

Thanks, that's helpful - do you have any advice on how I might solve the problem with sum(if()) ?

[sunny_talwar · Accepted solution]

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)

[ben_pugh]

Thanks Sunny, that's it!

I had tried something similar, but I used single speech marks ' instead of double "

[syed_muzammil]

Hi,

You can use Aggr function. Some thing like

Count( Distinct if(Aggr(Sum(Field_A),Day,Location) >0 ,Location)).

[ben_pugh]

Thanks Muzammil. I've already started development using Sunny's method above, but it's good to know that Aggr is an alternative.