Skip to Content
0

Webi 2-year Prior Average Issue

Mar 23, 2017 at 02:15 PM

49

avatar image
Former Member

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jyothirmayee A Mar 23, 2017 at 07:15 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 28, 2017 at 04:28 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

Post your formulas and the outcome pls.

Thanks,

Jothi

0