Hi,
I am trying to set individual rolling month flags within my master calendar with the following :
if(TempDate >floor(monthstart(addmonths(Today(),-13))) and TempDate< floor(monthend(addmonths(Today(),-12))),1,0) as Rolling13thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-12))) and TempDate< floor(monthend(addmonths(Today(),-11))),1,0) as Rolling12thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-11))) and TempDate< floor(monthend(addmonths(Today(),-10))),1,0) as Rolling11thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-10))) and TempDate< floor(monthend(addmonths(Today(),-9))),1,0) as Rolling10thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-9))) and TempDate< floor(monthend(addmonths(Today(),-8))),1,0) as Rolling9thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-8))) and TempDate< floor(monthend(addmonths(Today(),-7))),1,0) as Rolling8thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-7))) and TempDate< floor(monthend(addmonths(Today(),-6))),1,0) as Rolling7thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-6))) and TempDate< floor(monthend(addmonths(Today(),-5))),1,0) as Rolling6thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-5))) and TempDate< floor(monthend(addmonths(Today(),-4))),1,0) as Rolling5thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-4))) and TempDate< floor(monthend(addmonths(Today(),-3))),1,0) as Rolling4thMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-3))) and TempDate< floor(monthend(addmonths(Today(),-2))),1,0) as Rolling3rdMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-2))) and TempDate< floor(monthend(addmonths(Today(),-1))),1,0) as Rolling2ndMonthFlag,
if(TempDate >=floor(monthstart(addmonths(Today(),-1))) and TempDate< floor(monthend(addmonths(Today(),-0))),1,0) as Rolling1stMonthFlag
but it seems to be crossing over months :
| MonthYear | Rolling13thMonthFlag | Rolling12thMonthFlag |
|---|
| Feb-2016 | 1 | 0 |
| Mar-2016 | 1 | 1 |
Can anyone advise as to why this is occurring?
Many thanks
