COGNOiSe.com - The IBM Cognos Community

Hi ,

I wanted to filter accounts based on the filter condition , that the account should have sold more than 0 qty in the last 6 months.

so M Expression would be

Filter(Accounts, tuple([Qty],[6months])>0)

Here tuple expects a value for the months, whereas my months are like jun2015, july 2015, Aug2015,sep 2015 etc..

I dont want to use the overall member as it will have 12 months.

So the only way I know was to Add a member with expression jun2015+july2015+Nov2015 and then use it.

Is there a better approach to achieve this.

Thanks,
Nithya

This should help.

http://www-01.ibm.com/support/docview.wss?uid=swg21342263 (http://www-01.ibm.com/support/docview.wss?uid=swg21342263)

Quote from: nithya1224 on 16 Oct 2015 03:18:23 PM
Hi ,

I wanted to filter accounts based on the filter condition , that the account should have sold more than 0 qty in the last 6 months.

so M Expression would be

Filter(Accounts, tuple([Qty],[6months])>0)

Here tuple expects a value for the months, whereas my months are like jun2015, july 2015, Aug2015,sep 2015 etc..

I dont want to use the overall member as it will have 12 months.

So the only way I know was to Add a member with expression jun2015+july2015+Nov2015 and then use it.

Is there a better approach to achieve this.

Thanks,
Nithya

As you have correctly discovered, a tuple accepts only single members as the arguments. This is correct - a tuple is defined as the value at a specific intersection. You need a slightly different approach in your filter here:

Instead of tuple, try using a dimensional aggregate summary to aggregate your measure across the set of members you have:

eg

filter(Accounts, aggregate([Qty] within set [6 months]) > 0)

This assumes that [6 months] is a set of members...

Cheers!

MF.

Thanks a lot !! That sounds to be the right approach !!