Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

problem with filtering and aggregating

Hello my friends!

Please help me solve the following issue or at least point me in the correct direction.

I have following measure:

sum(if(aggr(count(distinct Medicine_Key),Pharm_id,Pharmacy_Chain)=GetPossibleCount(Medicine_Key),1,0))

In my model there is also such dimension as Period. I want to filter the model for 3 chosen months in a row. So if I choose, let's say, '9/1/2019','8/1/2019','7/1/2019', I receive the correct result based on formula above.

But I want to receive the same answer clicking only on 1 month (so, if I click on '9/1/2019', I should get answer based on formula above, but for this month and 2 months before, inclusive, like this: '9/1/2019','8/1/2019','7/1/2019').

I believe you would suggest me to use set expression, so please keep in mind that I have another filter with dimension Medicine_Key and its' filters should be taken into account.

Model looks like this. 

Capture.PNG

Please help me, how to do so?

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

Try putting the set analysis in your count as well: 

sum(

{$<[Period]={">=$(vMaxPeriodBack2)<=$(vMaxPeriod)"}>}

if(aggr(count({$<[Period]={">=$(vMaxPeriodBack2)<=$(vMaxPeriod)"}>}distinct Medicine_Key),Pharm_id,Pharmacy_Chain)=GetPossibleCount(Medicine_Key),1,0)

)

View solution in original post

4 Replies
Highlighted
Contributor II
Contributor II

I would make a variable for your max selected Period, one for you max selected Period - 2 months, and use set analysis to choose the Periods between these two variables.

eg:

varMaxPeriod = =max([Period])

varMaxPeriodBack2 = =date(if(GetSelectedCount([Period])>0 and GetSelectedCount([Period])<=3,AddMonths(max([Period]),-2),min([Period])),'M/D/YYYY')

set analysis expression:

{<[Period]={">=$(varMaxPeriodBack2)<=$(varMaxPeriod)"}

Highlighted
Contributor II
Contributor II

Unfortunately it keeps counting the values only for 1 month ignoring 2 months before this one.

Maybe the problem somewhere in syntax?

sum(

{$<[Period]={">=$(vMaxPeriodBack2)<=$(vMaxPeriod)"}>}

if(aggr(count(distinct Medicine_Key),Pharm_id,Pharmacy_Chain)=GetPossibleCount(Medicine_Key),1,0)

)

vMaxPeriod: =Max(Period)

vMaxPeriodBack2: =date(if(GetSelectedCount([Period])>0 and GetSelectedCount([Period])<=3,AddMonths(max([Period]),-2),min([Period])),'MM/DD/YYYY')

Highlighted
Contributor II
Contributor II

Try putting the set analysis in your count as well: 

sum(

{$<[Period]={">=$(vMaxPeriodBack2)<=$(vMaxPeriod)"}>}

if(aggr(count({$<[Period]={">=$(vMaxPeriodBack2)<=$(vMaxPeriod)"}>}distinct Medicine_Key),Pharm_id,Pharmacy_Chain)=GetPossibleCount(Medicine_Key),1,0)

)

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks! It worked perfectly!