# 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

## 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.

Please help me, how to do so?

Labels (2)

• ### Set Analysis Expression

1 Solution

Accepted Solutions
Highlighted
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)

)

4 Replies
Highlighted
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

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

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)

)

Highlighted
Contributor II

Thanks! It worked perfectly!