Print Page - filter accounts if last two month sales is zero

Title: filter accounts if last two month sales is zero
Post by: cognos05 on 26 Oct 2015 07:16:39 PM

Hi,

I am filtering accounts whose last 2 month sales is zero using expression

Filter(accounts , aggregate(currentMeasure with in set([last2Months])=0)

This works fine but when there is data like

Aug 2015 Sep 2015
Account1 0 0
Account2 -5 5

-5 and 5 then sum of these 2 months becomes 0 , but I dont want to filter these accounts meaning there was some transaction in these accounts.

So how can I get the same using other filter criteria

Thanks,
Nithya

Title: Re: filter accounts if last two month sales is zero
Post by: BigChris on 27 Oct 2015 01:56:23 AM

Can you calculate the absolute value of the sales, so that -10 becomes 10 for example? Then use that in your filter statement...

Title: Re: filter accounts if last two month sales is zero
Post by: cognos05 on 27 Oct 2015 08:19:06 AM

Yes ( abs (measure) within set (last2months)) worked !!

Thanks,
Nithya

Title: Re: filter accounts if last two month sales is zero
Post by: BigChris on 27 Oct 2015 08:42:07 AM

Excellent

Title: Re: filter accounts if last two month sales is zero
Post by: colt on 27 Oct 2015 09:45:24 AM

It's obvious:
Instead of Filtering: aggregate(currentMeasure with in set([last2Months])=0
You should use Expression: (Aggregate(currentMeasure within set([lastMonth])=0 AND Aggregate(currentMeasure within set([l2Month])=0)

But even then: if there are Transactions within 1 month that total up to 0 and this is the case for both months, then they will be filtered.
Therfore it might be better to use instead Maximum(Abs(currentMeasure within set([last2Months])) > 0

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: cognos05 on 26 Oct 2015 07:16:39 PM