Compatibility week number & master calendar based on MonthYear

[philgood34]

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

[avkeep01]

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

[philgood34]

here is it

[avkeep01]

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.

[philgood34]

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 ...

[avkeep01]

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?

[philgood34]

Hi

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