2 Replies Latest reply on Aug 20, 2018 10:24 AM by Deepak Rai

# Calculating Month over Month Ratio every 6 months

I have fields called Months and Value. Months is actually a discrete dimension and is plotted as 0 to 20 on the x axis. Percentage value is on the y axis. I want to create a calculated field for a month-over-month ratio for every 6 months. So the first calculation will be (Month 7)/(Month 6), next it should show a drop in (month 13)/(month 12) and so on. I tried using LOOKUP([Value], 8)/LOOKUP([Value], 7) but it seems to be returning incorrect values. Any help will be appreciated. Note: month is plotted as dimension.

 Months value 1 94.06% 2 92.00% 3 87.88% 4 85.29% 5 82.95% 6 81.66% 7 77.12% 8 76.08% 9 74.98% 10 73.79% 11 72.63% 12 71.99% 13 66.37% 14 65.50% 15 64.66% 16 63.83% 17 63.23% 18 62.85% 19 60.35% 20 59.83%
• ###### 1. Re: Calculating Month over Month Ratio every 6 months

Malvika,

is this what you are looking for.

I get the numbers with

if first() != 0 then

if ATTR( [Months] )%6 = 1 then   SUM([Value]) / lookup(SUM([Value]), -1)  end

end

Basically, except for the first month,  for every 7th month (modulo 6 = 1) then  divide the current value by the value of the precedent row.

Michel

• ###### 2. Re: Calculating Month over Month Ratio every 6 months

Hi Malvika,

Here is another way. I used a Parameter and This

to get this

Thanks

Deepak

1 of 1 people found this helpful