Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Attach sample data from your monthly extract
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
Attached
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.
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
This is perfect! Worked like a charm
Thank you so much!