Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
nithin_miryala
Contributor

Rolling 12 Months Flag not working.

Hi Everyone,

I need to create a flag in the Data modal for Rolling 12 months with respect to Current Quarter start.

So to test the expression i have wrote it in a list box and the logic feels correct to me and i connot figure what is wrong here,

Expression:

if(date(MonthYear)>=Addmonths(QuarterStart( Max(MonthYear)),-12) and date(MonthYear)<QuarterStart( Max(MonthYear)) ,1,0)

According to expression all the Dates between 10/1/2016 and 10/1/2017 should be 1 and rest of them should be 0

Capture.JPG

I have also attached the Test application.

Please kindly let me know you thoughts.

Thanks

Nithin

1 Solution

Accepted Solutions

Re: Rolling 12 Months Flag not working.

Try this

=if(date(MonthYear)>=Addmonths(QuarterStart( Max(TOTAL MonthYear)),-12) and date(MonthYear)<QuarterStart( Max(TOTAL MonthYear)) ,1,0)

Since list box has MonthYear as dimension, the Max(MonthYear) = MonthYear.... to get Max MonthYear across all MonthYear, you need to use TOTAL Qualifier.

6 Replies

Re: Rolling 12 Months Flag not working.

Try this

=if(date(MonthYear)>=Addmonths(QuarterStart( Max(TOTAL MonthYear)),-12) and date(MonthYear)<QuarterStart( Max(TOTAL MonthYear)) ,1,0)

Since list box has MonthYear as dimension, the Max(MonthYear) = MonthYear.... to get Max MonthYear across all MonthYear, you need to use TOTAL Qualifier.

nithin_miryala
Contributor

Re: Rolling 12 Months Flag not working.

Good one .

Thanks

nithin_miryala
Contributor

Re: Rolling 12 Months Flag not working.

i tried to write the expression in backend in the following manner.

CalendarMax=10/1/2017

if(DOSDateDT>=Addmonths(QuarterStart( $(CalendarMax)),-12) and DOSDateDT<QuarterStart( $(CalendarMax)) ,1,0) as Rolling12Flag

but still the column returns 0.

Any suggestions?

Re: Rolling 12 Months Flag not working.

Try with adding single quotes around your variable

if(DOSDateDT>=Addmonths(QuarterStart('$(CalendarMax)'),-12) and DOSDateDT<QuarterStart('$(CalendarMax)') ,1,0) as Rolling12Flag

nithin_miryala
Contributor

Re: Rolling 12 Months Flag not working.

Thanks a lot. I missed it

Re: Rolling 12 Months Flag not working.

No problem at all