All transaction UP TO Flag

[ericdelaqua]

Hello,

I need some help with my master calendar. I am trying to add 3 flag. so that when the user selects this flags all transactions are selected except current,last,last2 months record respectively.

All dates UP_TO_lastMonth_Flag

All dates UP_TO_2Months_Ago_Flag

All dates UP_TO_3Months_Ago_Flag

My calendar code is below. Thanks

Let vToday = num(Today());

MasterCalendar: 

Load 

 Date(TempDate,'DD MMM YYYY') AS Date, 

 week(TempDate) As Week, 

 Year(TempDate) As Year, 

 Month(TempDate) As Month,

  Year(TempDate) &'-'& Month(TempDate) as YearMonth,

  Year(AddMonths(TempDate,6)) as FinYr,

   Num(Month(AddMonths(TempDate,6)),'00') as FinPeriod,

 Day(TempDate) As Day, 

 'Q' & ceil(month(TempDate) / 3) AS Quarter, 

 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

 Year(TempDate) & '-' & Week(TempDate) as YearWeek,

 WeekDay(TempDate) as WeekDay,

 ///Flags

If(TempDate>$(vToday),1,0) as FutureFlag,

If(TempDate=$(vToday),1,0) as TodaysFlag,

InWeekToDate(TempDate, WeekEnd(Today()), 0)* -1 as CurrentWeekFlag,

InWeekToDate(TempDate, WeekEnd(Today()), -1)* -1 as PreviousWeekFlag,

If( InMonth (TempDate, $(vToday), +1),1) as NextMonthFlag,

inyeartodate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

inyeartodate(TempDate,$(vToday),-1) * -1 as LastYTDFlag,

inmonthtodate(TempDate,$(vToday),0) * -1 as CurMTDFlag,

inmonthtodate(TempDate,$(vToday),+1) * -1 as NextMTDFlag,

inmonthtodate(TempDate,$(vToday),-1) * -1 as LastMTDFlag,

If( InMonth (TempDate, $(vToday),0),1,0) as This_Month_Flag

;

//=== Generate a temp table of dates === 

LOAD 

 date(mindate + IterNo()) AS TempDate

 ,maxdate // Used in InYearToDate() above, but not kept 

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

 min(FieldValue('Date', recno()))-1 as mindate,

 max(FieldValue('Date', recno())) as maxdate

AUTOGENERATE FieldValueCount('Date');

[MayilVahanan · Accepted solution]

HI @ericdelaqua 

Try like below

Assign 3 variables

Let vUP_TO_lastMonth_Flag = Floor(MonthEnd(Today(),-1));
Let vUP_TO_2Months_Ago_Flag = Floor(MonthEnd(Today(),-2));
Let vUP_TO_3Months_Ago_Flag = Floor(MonthEnd(Today(),-3));

Add below lines in master calendar

Load *,

if(TempDate <= $(vUP_TO_lastMonth_Flag), 1, 0) as UP_TO_lastMonth_Flag,
if(TempDate <= $(vUP_TO_2Months_Ago_Flag), 1, 0) as UP_TO_2Months_Ago_Flag,
if(TempDate <= $(vUP_TO_3Months_Ago_Flag), 1, 0) as UP_TO_3Months_Ago_Flag,

[ericdelaqua]

Thanks @MayilVahanan ,

This is the solution I was after.