2 Replies Latest reply: Aug 30, 2016 8:18 AM by Kevin Warren RSS

    Rolling Calendar - 1,0 Identifier Flags Not Working as Intended

    Kevin Warren

      Help:

       

      I cannot seem to flag Last12months field column successfully in my script to output 1's in the appropriate rows, which is making the following expression unsuccessful.

       

      sum(aggr(sum({$<Last12MonthsFlag={1},Category = {'Income', 'Interest Income', 'Paycheck','Federal Tax','State Tax'}>} Amount), MonthYear))


      Below is the script Detail that is also in the app.


      LOAD

          "Date",

          Amount,

          "Transaction Type",

          Category,

          "Account Name"

      FROM [lib://Data/mint_transactional_data_masked.xlsx]

      (ooxml, embedded labels, table is transactions);

       

       

      MinMaxTemp:

      LOAD

          MIN(Date) as MinDate,

          MAX(Date) as MaxDate

      RESIDENT transactions

      ;

       

       

          LET vMinDate = NUM(PEEK('MinDate',0,'MinMaxTemp'));

          LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxTemp'));

          LET vToday = $(vMaxDate);

      ;

       

       

      CalTemp:

      LOAD

          DATE($(vMinDate) + ROWNO() - 1) as TempDate

        AUTOGENERATE

                $(vMaxDate) - $(vMinDate) + 1;

              

      DROP TABLE MinMaxTemp

      ;

       

       

      MasterCalendar:

      Load

          TempDate as Date,

          YEAR(TempDate) as Year,

          Month(TempDate) as Month,

          Month(TempDate)&'-'&Year(TempDate) AS MonthYear,

        // Month() as DisplayMonth,

          Week(TempDate) as Week,

          Day(TempDate) as Day,

          'Q' & Ceil(Month(TempDate)/3) as Quarter,

        

          InYearToDate(TempDate,$(vToday),0) * -1 as CYTDFlag,

          InYearToDate(TempDate,$(vToday),-1) * -1 as LYTDFlag,  

        

          If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)),1,0) as IsInYTD,

          If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)),1,0,) as IsInQTD,

          If(Month(TempDate) = Month($(vToday)),1,0) as IsCurrentMonth,

          If(Month(AddMonths(TempDate,1)) = Month($(vToday)),1,0) as IsLastMonth

        

      RESIDENT CalTemp

       

       

      ORDER BY TempDate ASC

      ;

       

       

      Drop Table CalTemp;

       

       

      // 1. Create a distinct list of Months:

      TransactionMonths:

      Load distinct

      Month

      Resident MasterCalendar

      ORDER BY Month ASC;

      ;

       

      // 2. Create a distinct list of DisplayMonths:

      DisplayMonths:

      Load

      Month as DisplayMonth

      Resident

      TransactionMonths

      ;

       

      // 3. Create a list of all possible combinations:

      join (TransactionMonths) load * resident DisplayMonths

      ;

       

       

      // 4. Reload the same table and calculate all the necessary flags:

      MonthsLink:

      Load

      Month,

      IF( Month >= AddMonths(DisplayMonth, -6) , 1, 0) as Last6MonthsFlag,

      IF( Month >= AddMonths(DisplayMonth, -12) , 1, 0) as Last12MonthsFlag,

      IF( Month = DisplayMonth , 1, 0) as SameMonthFlag

      Resident

      TransactionMonths

      ;

      drop table TransactionMonths;

      drop table DisplayMonths;