cancel
Showing results for 
Search instead for 
Did you mean: 

Webi 2-year Prior Average Issue

Former Member
0 Kudos

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!

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

Hi,

Post your formulas and the outcome pls.

Thanks,

Jothi

jyothirmayee_s
Active Contributor
0 Kudos

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