How to split monthly data into weekly

[torpe999]

Hello, could someone please help me to write a script how to split monthly data for different SKU and Customers into weekly data. If the week number is covering two months, I need to assign  week to the month which has more days. I have never worked with master calendar before and i am lack of knowledge how to start it :(. 

Thanks!

Giedrius

[Taoufiq_Zarra]

can you share the expected  output from this sample data?

[torpe999]

hi, i would like to get them in attached format. I have actual  sales data in years and weeks but planned volume in years and  months. I need to compare weekly data  so I need to convert planned monthly volumes into weekly and compare data by week. 

Thanks,

Giedrius

[johnca]

I'm sorry, but unless you have a date field you can place into a specific week, i.e. 1/17/2019, you cannot. In your data you only have month number.

HTH, John

[torpe999]

just one more thing- maybe it's possible to split monthly volumes into days proportionally first and then define the weeks?

Thanks!

Giedrius

[Taoufiq_Zarra]

I can propose a solution, I need you to confirm something:
For February 1, 2020, the week number is 5, so for February you'll be BU Litre/5 instead of 4.

if you can give me a quick feedback it will be excellent.

[torpe999]

That's fine :), thanks!

[Taoufiq_Zarra]

so /5 for 02/2020 ?

[torpe999]

yes, ok!

[Taoufiq_Zarra · Accepted solution]

the script is a bit long, I did it quickly but you can optimize it if you want.

Data:
LOAD 
     year, 
     month,
     weekstart(Makedate(year,month,1)) as DATESTART,
     Monthend(Makedate(year,month,1)) as DATEEND, 
     customer, 
     [SKU nr], 
     [BU liters]
FROM
.\litrai.xlsx
(ooxml, embedded labels, table is litrai);


temp:

load min(DATESTART) as min, max(DATEEND) as max resident Data;

Let varMinDate = Peek('min', 0, 'temp');
Let varMaxDate = Peek('max', 0, 'temp'); 


TempCalendar:
noconcatenate
LOAD

    Date($(varMinDate) + IterNo() - 1) as DATETMP

    AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

left join
load * resident Data;

drop table temp, Data;


output:
noconcatenate

load year, 
     month,
     if(DATETMP>=DATESTART and DATETMP<=DATEEND,week(DATETMP)) as WEEK,
     DATESTART,
     DATEEND,
     customer, 
     [SKU nr], 
     [BU liters]
     resident TempCalendar;

drop table TempCalendar;


output1:
noconcatenate

load   
       year, 
       month,
       WEEK,
     customer, 
     [SKU nr], 
     [BU liters]
     resident output where not isnull(WEEK);

drop table output;


Final1 :
noconcatenate

load count(distinct WEEK) as w resident output1 group by year,month,customer,[SKU nr];

left join

load * resident output1;

drop table output1;


result:

load  
      year, 
      WEEK,
     month,
     customer, 
     [SKU nr], 
     [BU liters],
     [BU liters]/w as [NEW BU liters] 

resident Final1;
     
drop table Final1;

and output :

attached Qlikview file