2-Year Prior average based on userresponse Fiscal Year

building reports webintelligence legacy
1

Hi everyone,

So I am creating a report in webi for a client who needs to see a crosstab with dimensions (1) org code (2) fiscal periods and (3) ytd expense as the measure. They also would like to see the 2-year prior average expense based on User Response(fiscal year). For 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.

landocarlesien (BOB member since 2017-03-28)

2

If i understood your question correctly, You need to use miltiple combinations of varibales for this

  1. variable to segregate each year
  2. variable to calculate avg for each year
  3. take average for raely average

In case you have any sample data and expected output i can try from my end

yatin3003 (BOB member since 2017-03-08)