3 Replies Latest reply on Jan 15, 2015 8:07 AM by FTisiot

Ratio at beginning of period

Hello

I have a requirement to the ratio of average annual salary per year relative to the initial year.

For example the reporting period 2012 -2014:

1/2012 500

2/2012 600

3/2012 700

....

The annual average beginning of the period is 600

1/2013 400

2/2013 470

3/2013 520

...

Annual average for 2013 is 463

2014 ...

The ratio in 2012 is of course 0

The ratio in 2013 is

(463-600) / 600 = -0.22

I've set up a field report BEGINING SALARY =

case when YEAR = min (YEAR by 1) then SALARY else NULL end

I received a correct result in the first year - year minimum

Now I'm trying to make a subtraction of this data every year and I can not.

Or I get a null value or I get wrong result

Depending on the aggrigation rule change

Anyone know of a fundamental solution to the problem. I have a lot of reports that I have to use such a ratio

Thank you!

• 1. Re: Ratio at beginning of period

Hi,

If ratio is always between Y, Y-1 and Y-2 you could:

a) Create the measure "Avg Salary"

b) Create the measure "Avg Salary Y-1" with the Ago Function based on Avg Salary

c) Create the measure "Avg Salary Y-2" with the Ago Function based on Avg Salary

d) Create the measure Ratio Y-1/Y-2 as 1-("Avg Salary Y-1"/"Avg Salary Y-2")

e) Create the measure RationY/Y-2 as 1-("Avg Salary Y"/"Avg Salary Y-2")

If you want to extend this method to an infinite nr of years and use ALWAYS by default the 1st year you can create a measure named "Avg Frist Year" that has First Aggregation method on the time dimension while keeping the Avg on all the other dimensions. Set the level to Dim Date Total. Then divide the Avg Value of Current Year by the Avg First Year.

• 2. Re: Ratio at beginning of period

Thank

The demand is dynamic, so the first solution does not meet the requirement.

Dם you have another idea to implement the report?

I do not want to solve it through the ADMIN because this is a lot of fields that I'll have to make this change

• 3. Re: Ratio at beginning of period

Can you try with what suggested before?

If you want to extend this method to an infinite nr of years and use ALWAYS by default the 1st year you can create a measure named "Avg Frist Year" that has First Aggregation method on the time dimension while keeping the Avg on all the other dimensions. Set the level to Dim Date Total. Then divide the Avg Value of Current Year by the Avg First Year.

You could also solve this in the front-end by doing something like

"Measure"."Salary" *100/SUM(CASE WHEN RANK("Dim Date"."Year" ASC) =1 then "Measure"."Salary" end)