Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

To create weekly data from Monthly extract

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

1 Solution

Accepted Solutions
Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

;

comm.png

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

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

6 Replies
YoussefBelloum
Champion
Champion

Hi,

Attach sample data from your monthly extract

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

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

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

Attached

Anonymous
Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

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

;

comm.png

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

Aurélien

Help users find answers! Don't forget to mark a solution that worked for you!
Anonymous
Not applicable
Author

This is perfect! Worked like a charm

Thank you so much!