Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I try to learn about CURRENT & PREVIOUS YEARS with a AsofTable in script
i have 1 tab
From another thread i imported and adapted this script
[DATA]:
LOAD Date(MakeDate([YEAR], [MONTH]), 'MM_YYYY') as MonthYear,
[YEAR],
[MONTH],
[SECTORS],
[SALES]
FROM [lib://RANK_FLAG/DATAS.xlsx]
(ooxml, embedded labels, table is DATA);
AsOfTable:
LOAD MonthYear as AsOfMonthYear,
MonthYear,
'CY' as Flag
Resident [DATA];
Concatenate (AsOfTable)
LOAD MonthYear as AsOfMonthYear,
Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,
'PY' as Flag
Resident [DATA];
Set vFM = 1;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(AsOfMonthYear) as minDate,
max(AsOfMonthYear) as maxDate
Resident AsOfTable;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
LOAD Year + If(Month >= $(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load
TempDate AS AsOfMonthYear,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
The result is :
NO PREVIOUS VALUES 😞
and this
Previous = current
Can you help me to understand with a right script please ?
thank's by advance
Philippe