Announcements
cancel
Showing results for
Did you mean:
Creator

## Set Analysis problem

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.

1 Solution

Accepted Solutions
MVP

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)

6 Replies
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Creator
Author

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

MVP

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)

Creator
Author

Thanks Sunny, that's it!

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

Partner - Creator II

Hi,

You can use Aggr function. Some thing like

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

Creator
Author

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

Community Browser