Accumulated values, KPI

[markusblaut]

Hello again,

I would like to compare the accumulated prices of 2018 (Jan - April) with the accumulated price from Jan - April of 2017.

so the calculation would be (20+30+50+50) / (50+60+40+70).

If there exists a price in May 2018, the formula should compare Jan-May 18 with Jan-May 17 and so on...

My formula unfortunately does not work

(rangesum(above(sum([Price2018]),0,RowNo())))

/

Sum(Aggr(If(Sum([Price 18]) > 0, Sum([Price 17])),AccountingMonth))

Tank you a lot in advance for any help!

Best

Markus

[sunny_talwar]

The chart you are trying to compare them on, have AccountingMonth as the dimension (just like your image above)?

[markusblaut]

Hi Sunny,

no chart, I would like to calculate an KPI. But my data is structured like in the example above.

so  (20+30+50+50) / (50+60+40+70 = 68,18%

Many thanks!

[sunny_talwar]

Okay... to make this work... you would need a month field which is only month information and no year information in it. Do you have such a field in your dashboard?

[sunny_talwar]

Also, do you have two different Prices for 17 and 18 in your database? or do you have a single Price field?

[markusblaut]

It is like this: so a month field exists, and there are two price fields

[markusblaut]

201710 is fiscal year 18 in our company that's why the values in 2018...