Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shmkmr90
Contributor III
Contributor III

Rolling N months over a calculated dimension

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

5 Replies
shiveshsingh
Master
Master

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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

It is not working Shivesh

shiveshsingh
Master
Master

Can you share sample app?