topic To create weekly data from Monthly extract in QlikView

To create weekly data from Monthly extract

Anonymous — Fri, 17 Nov 2017 02:19:48 GMT

Hi Team,

I have a requirement in which i need to show monthly data at a weekly level in order to calculate moving weekly average.

For example:

in the attached example :

I have the data loaded monthly. I created a master calendar to show records weekly.

But the data is shown only for the first day of the month or for only one week in a month

In order to calculate the moving weekly average, each week must have some data.

so i need the data to be equally distributed in a month by weeks rather than in a year by months as in the attached file. so that i can calculate the weekly average.

Any help would be much appreciated.

Thanks,

Harshala

Re: To create weekly data from Monthly extract

YoussefBelloum — Fri, 17 Nov 2017 09:04:15 GMT

Hi,

Attach sample data from your monthly extract

Re: To create weekly data from Monthly extract

Aurelien_Martinez — Fri, 17 Nov 2017 09:37:44 GMT

Hi,

for the script :

LOAD id,

Product,

Value,

[Fiscal Month DESC],

CDate

FROM

C:\Users\AMartinez.STORENEXT\Downloads\FactX.qvd

(qvd);

cMax_Date:

LOAD

Max([CDate]) as cmax_date

Resident FactX;

cMin_Date:

LOAD

Min([CDate]) as cmin_date

Resident FactX;

varcMinDate = Peek('cmax_Date',0,'cMax_Date')

varcMinDate = Peek('cmin_date',0,'cMin_Date')*1;

varcMaxDate = Peek('cmax_date',0,'cMax_Date')*1;

DROP Tables cMax_Date,cMin_Date;

/*---------------------------Temp Calendar---------------------------*/

CTempCalendar:

LOAD

$(varcMinDate) + RecNo()-1 as cNum,

Date($(varcMinDate) + RecNo() - 1) as cTempDate

AutoGenerate $(varcMaxDate) - $(varcMinDate) +1;

/*---------------------------Master Calendar---------------------------*/

Inter:

IntervalMatch(cTempDate)

LOAD

CDate,

MonthEnd(CDate) as fin

Resident FactX

;

CMasterCalendar:

Load

trim(date(cTempDate,'MMDDYYYY')) as [%cDate],

date(cTempDate,'MM/DD/YYYY') as [cTempDate],

MonthName(cTempDate) as [cMonth],

Day(cTempDate) as cDay,

Week(cTempDate) as [cWeek],

Date(WeekEnd(makeweekdate(Year(cTempDate),Num(Week(cTempDate), '00'),3)),'MM/DD/YYYY') as [Week Date],

Year(cTempDate) as [cYear]

Resident Inter

Order By cTempDate ASC;

Drop Tables CTempCalendar, Inter;

//script

And for the expression :

sum(total <cMonth> Value) / Count(distinct TOTAL<cMonth> cWeek)

Aurélien

Re: To create weekly data from Monthly extract

Anonymous — Fri, 17 Nov 2017 20:20:30 GMT

Attached

Re: To create weekly data from Monthly extract

Anonymous — Fri, 17 Nov 2017 20:24:37 GMT

Hi,

Thank you for the response!

I tried your approach but the values are duplicating. The total value for the month of jan 2014 is 603 and feb 2014 is 603 , so the weekly the value should be 150.75 for both jan and feb where as its showing me 2502.4 for both Jan and Feb weeks.

I have attached the data source file with updated values for better comparison.

Re: To create weekly data from Monthly extract

Aurelien_Martinez — Tue, 21 Nov 2017 09:33:35 GMT

Hi,

New approach :

LOAD id, 
     Product, 
     Value, 
     Date([Fiscal Month DESC], 'DD/MM/YYYY') as CDate
FROM
C:\Users\AMartinez.STORENEXT\Downloads\FactX.qvd
(qvd);


cMax_Date:
LOAD 
     Max([CDate]) as cmax_date
     Resident FactX;


cMin_Date:
LOAD 
     Min([CDate]) as cmin_date
     Resident FactX;
    
let varcMinDate = Peek('cmin_date',0,'cMin_Date')*1;
let varcMaxDate = Peek('cmax_date',0,'cMax_Date')*1;

//DROP Tables cMax_Date,cMin_Date;

/*---------------------------Temp Calendar---------------------------*/
CTempCalendar: 
LOAD 
  $(varcMinDate) + RecNo()-1 as cNum, 
  MonthStart($(varcMinDate) + RecNo() - 1) as CDate,
  Week($(varcMinDate) + RecNo() - 1)  as Week,
  MonthName($(varcMinDate) + RecNo() - 1) as Month
AutoGenerate $(varcMaxDate) - $(varcMinDate) +1
;


Join(CTempCalendar)
LOAD Distinct
  CDate,
  Count(DISTINCT Week) as nbWeek,
  Month
Resident CTempCalendar
Group By CDate, Month
;

But there is 5 weeks in jan (603 / 5 = 120.6)

Aurélien

Re: To create weekly data from Monthly extract

Anonymous — Fri, 01 Dec 2017 23:27:51 GMT

This is perfect! Worked like a charm

Thank you so much!