- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Tags:
- aggr()
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)"}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks! It worked perfectly!