Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Last 12 months or Trailing Twelve Months flag on master calendar

Does anyone knows how to calculate a sum for the last 12 months of information?
Is there some flag like the current YTD or Previous YTD flag on the master calendar (InYearToDate(TempDate,$(varToday),0)* -1 as CurYTDFlag)?


Thanks in advance for your input!

1 Solution

Accepted Solutions
chematos
Valued Contributor II

Re: Last 12 months or Trailing Twelve Months flag on master calendar

You can create a master calendar how you want. If you dont have flags you can use your dates anyway.

sum({< Date={'>=$(=AddMonths(Today(),-12)'}>} FieldToSum)

This will accumulate the values of FieldToSum for the last 12 months, 14/07/2011 to 14/07/2012 in case of today.

3 Replies
chematos
Valued Contributor II

Re: Last 12 months or Trailing Twelve Months flag on master calendar

You can create a master calendar how you want. If you dont have flags you can use your dates anyway.

sum({< Date={'>=$(=AddMonths(Today(),-12)'}>} FieldToSum)

This will accumulate the values of FieldToSum for the last 12 months, 14/07/2011 to 14/07/2012 in case of today.

Not applicable

Re: Last 12 months or Trailing Twelve Months flag on master calendar

Muchas gracias Jose Maria!

Your AddMonths formula worked perfectly!
What I did with it, was to create a flag in the master calendar when a date is on the 12 Months range, so in my set analysis I can only say TTMFlag = 1 (sum({<TTMFlag = {1}>}FieldToSum)

Here's how I created the load script just in case it helps you
Regards,

LET varToday = Num(Today());

LET varTTM = Num(AddMonths($(varToday),-12));

if(TempDate> $(varTTM),1,0) as TTMFlag

Highlighted
utdyt
New Contributor II

Re: Last 12 months or Trailing Twelve Months flag on master calendar

Thank's for your post, that really helped me a lot.

😄