Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ben_pugh
Creator
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:

  

LOCATIONMONTUEWED
A111
A100
A101
B110
B000
C110

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
sunny_talwar

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)

View solution in original post

6 Replies
jonathandienst
Partner - Champion III
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
ben_pugh
Creator
Creator
Author

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

sunny_talwar

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
Creator
Creator
Author

Thanks Sunny, that's it!

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

syed_muzammil
Partner - Creator II
Partner - Creator II

Hi,

You can use Aggr function. Some thing like

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

ben_pugh
Creator
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.