To create weekly data from Monthly extract

[Anonymous]

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

[YoussefBelloum]

Hi,

Attach sample data from your monthly extract

[Aurelien_Martinez]

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

[Anonymous]

Attached

[Anonymous]

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.

[Aurelien_Martinez · Accepted solution]

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

[Anonymous]

This is perfect! Worked like a charm

Thank you so much!