Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Good one .
Thanks
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?
Try with adding single quotes around your variable
if(DOSDateDT>=Addmonths(QuarterStart('$(CalendarMax)'),-12) and DOSDateDT<QuarterStart('$(CalendarMax)') ,1,0) as Rolling12Flag
Thanks a lot. I missed it
No problem at all