Determining Average Sales for the Last 6 Weeks using Over() Partition

Database

Determining Average Sales for the Last 6 Weeks using Over() Partition

Hello, I am trying to determine average sales for the last 6 weeks using the  following:

 

select
b.FISC_EOW_DT,
a.*,
avg(net_unit_qty) over (partition by sid order by FISC_EOW_DT rows between 5 preceding and current row) as avsaleslast6wk

FROM tbl1 a
JOIN (SELECT DISTINCT FISC_WK_OF_MTH_ID,FISC_EOW_DT FROM tbl2 ) B
ON B.FISC_WK_OF_MTH_ID=A.FISC_WK_OF_MTH_ID
where sid = 12345

 

This works however it calculates the last 5 rows, regardless if they are the previous week or not. So for example:

 

if the weeks was:

12/01/2016

01/08/2017

06/01/2017

08/01/2017

 

It will calculate the average of these 4 weeks even though they are not consecutive. I need to know how to calculate the Average sales including weeks that are not consecutive. So for the week going back from:

 

01/08/2017

to

06/01/2017

 

 

 

there would be 0 for average sales since the last 6 weeks are not represented.

 

Any help would be greatly appreciated.

2 REPLIES
Senior Supporter

Re: Determining Average Sales for the Last 6 Weeks using Over() Partition

as the syntax states - and you already noticed - it is dealing with rows and not with periods.

"rows between 5 preceding and current row"

the documentation refer this as "Ensure that data you analyze has no missing data points. Computing a moving function over data with
missing points produces unexpected and incorrect results because the computation considers n physical
rows of data rather than n logical data points."

 

This means you have to create the missing week infos yourself. try something like

SELECT b.FISC_EOW_DT,
       a.*,
       AVG(zeroifnull(a.net_unit_qty) OVER (PARTITION BY b.sid ORDER BY b.FISC_EOW_DT rows BETWEEN 5 preceding AND CURRENT row) AS avsaleslast6wk
FROM tbl1 a
  right outer JOIN 
  (SELECT t1.sid, t2.FISC_WK_OF_MTH_ID, t2.FISC_EOW_DT 
        FROM tbl2 as t2
        cross join 
        select t2.sid 
        from tbl1 as t1
        group by t1.sid, t2.FISC_WK_OF_MTH_ID, t2.FISC_EOW_DT 
        ) B 
        ON a.sid = b.sid 
           and B.FISC_WK_OF_MTH_ID = A.FISC_WK_OF_MTH_ID
WHERE sid = 12345
qualify a.sid is not null
;

 

Highlighted

Re: Determining Average Sales for the Last 6 Weeks using Over() Partition

Hello,

 

Thank you for the reply. You are right, I figured I will need to populate the months so that there are no inconsistencies in the dates. I have tried to run the code that you provided and it is kicking out the error:

 

SELECT Failed. 3707:  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'join' keyword and the 'select' keyword. 

 

I think this is in reference to the cross join, but when I try to close the statement it still gives an error. Also, t2 does not have sid in the table, I noticed this in the code. Thank you for you help.