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,
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:
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
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.