Former Member

# Need a measure: Average Sales = Last 4 week sales / 4

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

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Aug 24, 2011 at 10:01 PM

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.

10|10000 characters needed characters exceeded
• Former Member
Posted on Aug 24, 2011 at 05:14 AM

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.

10|10000 characters needed characters exceeded
• Former Member
Posted on Aug 24, 2011 at 01:20 PM

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.

10|10000 characters needed characters exceeded
• Former Member

hi

you will get only week numbers for that formula,how to get sales revenue corresponding that week number

Regards