# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:  Contributor II

## problem with filtering and aggregating

Hello my friends!

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. Labels (1)
• ### Set Analysis

1 Solution

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

set analysis expression:

{<[Period]={">=\$(varMaxPeriodBack2)<=\$(varMaxPeriod)"}  Contributor II
Author

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)  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)

)  Contributor II
Author

Thanks! It worked perfectly! Tags
Community Browser