CY & PY AsofTAble in mastercalendar

philgood34 — Wed, 18 Oct 2017 17:38:11 GMT

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

topic CY & PY AsofTAble in mastercalendar in App Development

CY & PY AsofTAble in mastercalendar