Calculating average for rolling four weeks of data.

General

Calculating average for rolling four weeks of data.

Hello,

I have an existing report, to which I need add average of net sales for past four weeks for any given day.

Eg; If we are taking monday of given week, we need to calculate the average of net sales amount for past four weeks.

To calculate the net sales for different catrgories in the existing code, they used the following function:

-- SUM(NetSales_Amt) OVER (PARTITION BY A, B, C, D  ORDER BY Business_Dt ROWS UNBOUNDED PRECEDING) AS Netsales.

I have modified the above query to add the where clause, as where business_dt = current_date -1 to get records for one day.

Similary how do I get the results and average of previous four weeks same day.

I tought of using business_dt = current_date -8, business_dt = current_date - 15. business_dt = current_date - 22.

But not sure how to implement it and calculate the average.

Can some one please help me with this, or provide me with a better solution if possible.

Thanks in advance,

Praveen.

3 REPLIES
N/A

Re: Calculating average for rolling four weeks of data.

Hi Praveen,

i don't understand exactly what you're trying to achieve.

The OLAP SUM returns a culumative sum/running total, but your narration talks about the same day within the last four weeks based on today:

select avg(NetSales_amt)

from tab

where business_dt = current_date

   or business_dt = current_date-8

   or business_dt = current_date-15

   or business_dt = current_date-22

group by 1

Dieter

Re: Calculating average for rolling four weeks of data.

Hello Dieter,

The Avg. of net sales should be calculated for all the 4 weeks of data for any given data i.e,

avg of net sales on business_dt = current_date

  and  business_dt = current_date-8

 and  business_dt = current_date-15

and business_dt = current_date-22,

like (week1 net sales + week2 net sales + week3 net sales + week4 net  sales)/4 . And this should be calculated for all the combinations of columns used in Partition BY clause above i.e(A,B,C,D)

I tried to use the below function

-- AVG(NetSales_Amt) OVER (PARTITION BY A, B, C, D  ORDER BY Business_Dt ROWS UNBOUNDED PRECEDING) AS Avg_sales

I inserted the data for all four days into a single table and calculated the avg as above . But that is not giving the expected result for average sales.

Re: Calculating average for rolling four weeks of data.

hello dnoeth

COULD YOU PLEASE HELP ME  HOW TO MOVE DATA FROM DBQL TO PDCR?