Skip to Content
author's profile photo Former Member
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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.