on 03-23-2017 2:15 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.