Rolling N months over a calculated dimension

[shmkmr90]

Hi,

Could anyone help me.

I want to calculate a rolling N months over a calculated dimension. i had tried multiple approaches but unlucky to succeed.

Calculated dimension: =aggr(sum(  INVOICE_QUANTITY), ORDER_NUMBER) labeled as 'Number of products'

expression tried out are:

=Count({<ORDER_DATE={">$(=addmonths(MonthsStart(1,Max(ORDER_DATE)),-11))<$(=Date(Max(ORDER_DATE)))"}>}distinct CUSTOMER_ID)

My requirement is Number of customers (expression) ordered this many products (calculated dimension) for a rolling time period, only year and month to be selected.

ORDER_DATE format is YYYY-MM-DD

SET DateFormat='DD.MM.YYYY'; (default in the script)

Thanks & Regards,

Shyam

+91 9600532969

shmkmr90@gmail.com

[shiveshsingh]

Try this

=Count({<ORDER_DATE={">$(=Date(addmonths(MonthStart(Max(ORDER_DATE)),-11),'YYYY-MM-DD')

<$(=Date(Max(ORDER_DATE),'YYYY-MM-DD')"}>}distinct CUSTOMER_ID)

[shmkmr90]

Thanks Shivesh for your reply,

I think i have tried this too.. anyways, will confirm you after trying out once more. couple of questions below for clarity.

Does your expression work for rolling 6 months and 3 months too.. if i change the to -5 and -2 in the current place of -11?

Will this expression work over a calculated aggr dimension (mentioned in intial post) without any field dimensions ,directly used in the straight table?

can anyone clear my doubts.

@anil babu @Piet Hein van der Stigchel

Thanks,

Shyam

[shiveshsingh]

Does your expression work for rolling 6 months and 3 months too.. if i change the to -5 and -2 in the current place of -11?

YES, and let me know once you test this expression

[shmkmr90]

It is not working Shivesh

[shiveshsingh]

Can you share sample app?