Dear all.
We need to implement one formula in WebI report :
Average Sales = Last 4 week sales / 4
It must be dynamic i.e.
Week number of the Year Avg Sales of Weeks
34.2011 (313233+34)/4
35.2011 (323334+35)/4
Eg: 52.2010 (49.201050.201051.2010+52.2010) /4
Layout of report is :
Customer Week Material Sales Average Sales of Last 4 Weeks
Cust1 24.2011 M X1 Avg for weeks (20.201121.201122.2011+23.2011)
Cust1 25.2011 M X2 Avg for weeks (21.201122.201123.2011+24.2011)
Cust1 26.2011 M X3 Avg for weeks (22.201123.201124.2011+25.2011)
Cust1 27.2011 M X4 Avg for weeks (23.201124.201125.2011+26.2011)
Cust2 24.2011 M Y1 Avg for weeks (20.201121.201122.2011+23.2011)
Cust2 25.2011 M Y2 Avg for weeks (21.201122.201123.2011+24.2011)
Cust2 26.2011 M Y3 Avg for weeks (22.201123.201124.2011+25.2011)
Cust2 27.2011 M Y4 Avg for weeks (23.201124.201125.2011+26.2011)
Request you to kindly provide inputs if you have any idea on how to implement this.
Thanks and Regards,
Ashmita
Hi Ashmita,
I think the RelativeValue function will help you. First of all, you need a version of your week dimension that sorts correctly. Use some string formulas (left, right, etc.) to get the format YYYY.WW.
Then use a formula like =(RelativeValue([[Revenue]]; ([[Week]]); -1) + RelativeValue([[Revenue]]; ([[Week]]); -2) + RelativeValue([[Revenue]]; ([[Week]]); -3) + RelativeValue([[Revenue]]; ([[Week]]); -4)) / 4
I haven't tested that, but look up the formula in help if I'm wrong and you're not familiar with it.
Hi all,
PFB more details:
- We are using BO-WebI SP3 and the Universe is based on Oracle tables.
- The requirement is dynamic i.e. we are not giving any inputs.
- It is a normal report which has drill down on WEEK,
- and we need another column, which displays the Avg Sales of last four week, corresponding to the Week in that Row.
- So, if row has Week: 42.2010 u2013 the value corresponding to it would be Avg Sales ( 41.201040.201039.2010+38.2010).
- The measure should not be bound to the Report layout.
- Even , if the report start from Week = xyz, against that column , Avg of previous weeks must come.
Any help would be much appreciated!
Thanks.
Hi,
How will you get the weekno .
Try like creating 3 variables at report vel which will be like this.
1. va1= weekno-1
2.var2 =var1-1
3.var3=var1-1
All the above variables should be measure and apply formula as
var_Avg = var1var2var3+weekno/4
If above doesn't work then try with RunningAvg ().
Cheers,
Suresh Aluri.
Add a comment