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;
