Skip to Content
avatar image
Former Member

Webi 2-year Prior Average Issue

Hi everyone,

So I am creating a report in webi for a client who needs to see a crosstab with dimensions org code and fiscal periods and ytd expense as the measure. They also would like to see the 2-year prior average expense for whichever year selected. An example: they have 5 periods selected and the current FY (2017) in the prompt summary. They need to see the 2-year prior average expense by code & period (2015 & 2016 2-year average).

I am having trouble creating variables to get the 2-year average. I created a second query that pulls in all years and periods, but i am not sure of the logic. The dimensions I have been using: (1) Fiscal Year (2) Posting Period (3) Code Group. Measure: (1) YTD Expenditures.

Any Thoughts? Thanks!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 23, 2017 at 07:15 PM

    Hi,

    1. capture Userresponse()

    Current Yr Flag

    If Userresponse() = Year(CurrentDate());1;0) = 2017

    2. Previous Year Flag:

    If Userresponse() = Year(CurrentDate())-1;1;0) =2016

    3. 2nd prior year flag

    If Userresponse() = Year(CurrentDate())-2;1;0) = 2015

    if you have string convert to number

    CY=FormatNumber((ToNumber(Userresponse()));"####")

    =FormatNumber((ToNumber([CY])-1);"####")

    Now try getting the average of the above 2 Previous Years:

    =RunningAverage([Measure];[Year Dims])

    Post sample data if possible.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 28, 2017 at 04:28 PM

    Hi Jothi,

    Thanks for the help. So once I create the flags you mentioned and change from a string to number, what else do I need? I created a second query and added all years and months to it. Just unable to get the 2 previous years averages based on user response. Thanks

    Add comment
    10|10000 characters needed characters exceeded