topic Re: Set Analysis problem in QlikView

Set Analysis problem

Ben_P — Thu, 07 Jun 2018 09:10:32 GMT

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.

Re: Set Analysis problem

jonathandienst — Thu, 07 Jun 2018 09:15:13 GMT

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.

Re: Set Analysis problem

Ben_P — Thu, 07 Jun 2018 09:28:48 GMT

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

Re: Set Analysis problem

sunny_talwar — Thu, 07 Jun 2018 10:27:58 GMT

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)

Re: Set Analysis problem

Ben_P — Thu, 07 Jun 2018 10:47:43 GMT

Thanks Sunny, that's it!

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

Re: Set Analysis problem

syed_muzammil — Thu, 07 Jun 2018 10:57:06 GMT

Hi,

You can use Aggr function. Some thing like

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

Re: Set Analysis problem

Ben_P — Fri, 08 Jun 2018 07:11:42 GMT

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