Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get this age of month sales expression?

How to get this age of month sales expression? Someone can show about my problem from this picture.

Untitled.jpg

I want to get AGE_OF_MONTH Expression. For example :

if YEAR = 2008 and PRODUCT = ABC SUSU. It's min(YEAR), so the result is Num(Month(END_DATE)) - Num(Month(START_DATE)) = 9.

if YEAR = 2009 and PRODUCT = ABC SUSU, YEAR -1 (Last Year / 2008) they found a START_DATE, the result is 12.

If YEAR = 2012 and PRODUCT = ABC SUSU, is the Max YEAR, so the result is Num(Month(END_DATE)) = 4.

but if YEAR = 2012 and PRODUCT = CACA, it's not found last year. So the result is Num(Month(END_DATE)) - Num(Month(START_DATE)) = 2.

Can someone to help me to make this expression?

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

This is the right code :

if(YEAR=min(TOTAL <PRODUCT> YEAR),num(month(END_DATE))-num(month(START_DATE)) + 1,

    if(YEAR=max(TOTAL <PRODUCT> YEAR),num(month(END_DATE)),12))

Thanks.

View solution in original post

2 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

attach is an example

hope it helps you

i got diffrent result for the first year  of producrs because if you do for example

num(month(END_DATE))-NUM(monnt(START_DTAE)) for abc susu in 2008 you get 8 and not 9

Not applicable
Author

This is the right code :

if(YEAR=min(TOTAL <PRODUCT> YEAR),num(month(END_DATE))-num(month(START_DATE)) + 1,

    if(YEAR=max(TOTAL <PRODUCT> YEAR),num(month(END_DATE)),12))

Thanks.