Create Master Calendar using Monthly Data

[Anonymous]

HI,

I was looking for a way to create a master Calendar. I need to take my Monthly forecast numbers and break that into weekly buckets. the lowest level of information date wise I have is month. Is this possible?

Example:

Jan: 12000

Would like to see:

Week1: 3000

Week2: 3000

Week3: 3000

Week4: 3000

Etc.

We essentially forecast in Monthly Buckets but do tactical planning in weekly buckets.

Thanks,

[YoussefBelloum]

Hi,

you can generate more details from your Month field going even to a timestamp, but:

how you want to calculate weekly forecast from monthly forecast ?

just divide the number by the number of the weeks of the month ?

[YoussefBelloum]

if you just want to divide the total amount by the numbers of week:

1. create a master calendar from the months you have:

[Feuil1]:

LOAD [Month],

            [forecast]

FROM [lib://Desktop/forecast.xlsx]

(ooxml, embedded labels, table is Feuil1);

Temp:

Load

              min(makedate(2018,Month,1)) as minDate,

              max(makedate(2018,Month,31)) as maxDate

Resident Feuil1;

Drop Table Feuil1;

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

              TempDate AS OrderDate,

              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,

              If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

              Week(TempDate) & '-' & Year(TempDate) as WeekYear,

              WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

2. on a table chart, do this:

put your month and weeks dimensions and measure like this:

dimension1: Month

dimension2: Week

Measure:

=sum(forecast)/count(total aggr(Count(Week),Week))

It is one way to do it..

[Anonymous]

Tried to load your script but got the error below in the TempCalendar Load:

[YoussefBelloum]

I tried my solution before suggesting it..

Maybe you are trying to load the first table ? If so, replace it with YOUR table.

Is it clear ?

[Anonymous]

I tried with my table and still didnt work- it still shows the error being on the TempCalendar for a field not recognized.

basically I have a file that has the below info:

Plant	Month	Value	Year
2194	Jan	16,265	2018
2194	Feb	16,115	2018
2194	Mar	20,078	2018
2194	Apr	28,122	2018
2194	May	33,383	2018
2194	Jun	35,062	2018
2194	Jul	38,824	2018
2194	Aug	36,059	2018
2194	Sep	36,935	2018
2194	Oct	41,881	2018
2194	Nov	34,347	2018
2194	Dec	26,361	2018

[Anil_Babu_Samineni]

Try this?

Fact:

LOAD *, Date(MakeDate(Year,Num(Month(Date#(Month,'MMM')))),'DD-MM-YYYY') as DateField From Table;

LOAD MinDate + IterNo() -1 as DateField

While MinDate + IterNo() -1 <= MaxDate;

LOAD Min(DateField) as MinDate,

Max(DateField) as MaxDate

Resident Fact;

[YoussefBelloum]

My bad,.. when i created a test table, I created a num Month_field, so you can try this:

test:

LOAD * Inline [

Plant,Month, Value, Year

2194,Jan, 16.265, 2018

2194, Feb, 16.115, 2018

2194, Mar, 20.078, 2018

2194, Apr, 28.122, 2018

2194, May, 33.383, 2018

2194, Jun, 35.062, 2018

2194, Jul, 38.824, 2018

2194, Aug, 36.059, 2018

2194, Sep, 36.935, 2018

2194, Oct, 41.881, 2018

2194, Nov, 34.347, 2018

2194, Dec, 26.361, 2018

](delimiter is ',');

EXIT SCRIPT;

[Feuil1]:

LOAD [Month],

            [Value]

Resident test;

Temp:

Load

              min(makedate(2018,num(month(date#(Month,'MMM'))),1)) as minDate,

              max(makedate(2018,num(month(date#(Month,'MMM'))),31)) as maxDate

Resident Feuil1;

Drop Table Feuil1;

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

              TempDate AS OrderDate,

              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,

              If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

              Week(TempDate) & '-' & Year(TempDate) as WeekYear,

              WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;