topic Re: Create Master Calendar using Monthly Data in QlikView

Create Master Calendar using Monthly Data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Create Master Calendar using Monthly Data

YoussefBelloum — Mon, 15 Jan 2018 22:29:34 GMT

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 ?

Re: Create Master Calendar using Monthly Data

YoussefBelloum — Mon, 15 Jan 2018 23:36:57 GMT

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

Re: Create Master Calendar using Monthly Data

Anonymous — Tue, 16 Jan 2018 12:49:40 GMT

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

Re: Create Master Calendar using Monthly Data

YoussefBelloum — Tue, 16 Jan 2018 12:55:09 GMT

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 ?

Re: Create Master Calendar using Monthly Data

Anonymous — Tue, 16 Jan 2018 13:14:27 GMT

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

Re: Create Master Calendar using Monthly Data

Anil_Babu_Samineni — Tue, 16 Jan 2018 13:30:55 GMT

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;

Re: Create Master Calendar using Monthly Data

YoussefBelloum — Tue, 16 Jan 2018 13:32:39 GMT

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;