topic Re: Compatibility week number & master calendar based on MonthYear in App Development

Compatibility week number & master calendar based on MonthYear

philgood34 — Thu, 31 May 2018 07:53:25 GMT

Hi everybody

I have a app with a master calendar . Until now, my needs was to create set analysis based on monthly data only

here is an extract from my script

[FP_R$]:

LOAD Date(MakeDate([FPR_ANN], [FPR_MOI]), 'MM_YYYY') as MonthYear,

[FPR_ANN],

[FPR_MOI],

[FPR_NCL] AS [NCL-VMS_NCL],

[FPR_TYPV],

[FPR_HRS],

[FPR_VHT],

[FPR_MTFP],

[FPR_COUHR],

[FPR_PFPCAHT],

[FPR_TXVULN]

FROM [lib://SAMPLE/PDFPR.xls]

(biff, embedded labels, table is FP_R$)

WHERE EXISTS ([NCL-VMS_NCL], [FPR_NCL]);

... .....

AsOfTable:

LOAD MonthYear as AsOfMonthYear,

MonthYear,

'CY' as Flag

Resident [CA_R$];

Concatenate (AsOfTable)

LOAD MonthYear as AsOfMonthYear,

Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,

'PY' as Flag

Resident [CA_R$];

Concatenate (AsOfTable)

LOAD MonthYear as AsOfMonthYear,

MonthYear,

'Goal' as Flag

Resident [CA_P$];

Set vFM = 4;

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;

I have now a new table to import with weekly data ( Dimension name for week is [FPH_SMN] )

I try this script

[FP_H$]:

LOAD Date(MakeDate([FPH_ANN],[FPH_SMN]), 'WW,YYYY') as WeekYear,

[FPH_ANN],

[FPH_SMN],

[FPH_NCL] AS [NCL-VMS_NCL],

[FPH_HRS],

[FPH_VHT_CIBLE],

[FPH_MTFP],

[FPH_HRS_CIBLE]

FROM [lib://SAMPLE/PDFPH.xls]

(biff, embedded labels, table is FP_H$)

unfortunately, i get this loading error

I think i have to actualize my master calendar script by how ???

Can you help me ?

Philippe

Re: Compatibility week number & master calendar based on MonthYear

avkeep01 — Thu, 31 May 2018 07:57:36 GMT

Hi Philippe, could you post a print screen of the datamodel. The gives us insight in which fields causes the loop.

Re: Compatibility week number & master calendar based on MonthYear

philgood34 — Thu, 31 May 2018 08:02:49 GMT

here is it

Re: Compatibility week number & master calendar based on MonthYear

avkeep01 — Thu, 31 May 2018 08:06:35 GMT

The easiest way is to rename / drop the field MonthYear from the asof table. that's causing the loop.

Or rename/drop the monthyear in the other tables. Not quite sure if the syntetic table is there on purpose or unintentionally.

Or maybe concat the NCL_VSM... en MonthYear into 1 key in the CA and FP tables.

Re: Compatibility week number & master calendar based on MonthYear

philgood34 — Thu, 31 May 2018 08:52:32 GMT

I'm afraid that changing my initial settings make troubles in all my visualizations ...

I was hoping it's there solution in master calendar setting as this thread says ...

Redefining the Week Numbers

But i dont' know how to adapt the advices to my issue ...

Re: Compatibility week number & master calendar based on MonthYear

avkeep01 — Thu, 31 May 2018 13:46:21 GMT

Hi Philippe, I don't think redefining the week numbers fixes your problem. Have you already tried renaming the monthyear field in the as of table?

Re: Compatibility week number & master calendar based on MonthYear

philgood34 — Thu, 31 May 2018 14:21:16 GMT

no i didn't

if i do that, how can i rename these part of my script in the other tables , for example, these one

[FP_R$]:

LOAD Date(MakeDate([FPR_ANN], [FPR_MOI]), 'MM_YYYY') as MonthYear,

[FPR_ANN],

[FPR_MOI],

[FPR_NCL] AS [NCL-VMS_NCL],

[FPR_TYPV],

[FPR_HRS],

[FPR_VHT],

[FPR_MTFP],

[FPR_COUHR],

[FPR_PFPCAHT],

[FPR_TXVULN]

FROM [lib://SAMPLE/PDFPR.xls]

(biff, embedded labels, table is FP_R$)

WHERE EXISTS ([NCL-VMS_NCL], [FPR_NCL]);

as you see i have only month and year fields in the others table; but not week field ..

and in the asoftable, can you explain how rename / drop the field MonthYear ?

thank's in advance

Philippe