topic Re: Rolling 12 Months Flag not working. in App Development

Rolling 12 Months Flag not working.

nithin_miryala — Mon, 11 Dec 2017 20:44:33 GMT

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

I have also attached the Test application.

Please kindly let me know you thoughts.

Thanks

Nithin

Re: Rolling 12 Months Flag not working.

sunny_talwar — Mon, 11 Dec 2017 20:47:47 GMT

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.

Re: Rolling 12 Months Flag not working.

nithin_miryala — Mon, 11 Dec 2017 20:53:44 GMT

Good one .

Thanks

Re: Rolling 12 Months Flag not working.

nithin_miryala — Mon, 11 Dec 2017 21:52:01 GMT

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.

sunny_talwar — Mon, 11 Dec 2017 21:54:34 GMT

Try with adding single quotes around your variable

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

Re: Rolling 12 Months Flag not working.

nithin_miryala — Mon, 11 Dec 2017 21:57:10 GMT

Thanks a lot. I missed it

Re: Rolling 12 Months Flag not working.

sunny_talwar — Mon, 11 Dec 2017 21:58:26 GMT

No problem at all