topic How to split monthly data into weekly in QlikView

How to split monthly data into weekly

torpe999 — Sat, 16 Nov 2024 19:09:10 GMT

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

Re: Hot to split monthly data into weekly

Taoufiq_Zarra — Sun, 09 Feb 2020 13:49:51 GMT

can you share the expected output from this sample data?

Re: How to split monthly data into weekly

torpe999 — Sun, 09 Feb 2020 18:03:52 GMT

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

Re: How to split monthly data into weekly

johnca — Mon, 10 Feb 2020 00:42:29 GMT

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

Re: How to split monthly data into weekly

torpe999 — Mon, 10 Feb 2020 08:31:39 GMT

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

Thanks!

Giedrius

Re: How to split monthly data into weekly

Taoufiq_Zarra — Mon, 10 Feb 2020 08:57:54 GMT

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.

Re: How to split monthly data into weekly

torpe999 — Mon, 10 Feb 2020 09:04:41 GMT

That's fine :), thanks!

Re: How to split monthly data into weekly

Taoufiq_Zarra — Mon, 10 Feb 2020 09:05:58 GMT

so /5 for 02/2020 ?

Re: How to split monthly data into weekly

torpe999 — Mon, 10 Feb 2020 09:08:52 GMT

yes, ok!

Re: How to split monthly data into weekly

Taoufiq_Zarra — Mon, 10 Feb 2020 09:24:46 GMT

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

Re: How to split monthly data into weekly

torpe999 — Mon, 10 Feb 2020 15:05:37 GMT

Thanks Taoufiq ZARRA, you helped a lot!

BR/Giedrius