Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

markusblaut
New Contributor III

Accumulated values, KPI

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

6 Replies
MVP
MVP

Re: Accumulated values, KPI

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

markusblaut
New Contributor III

Re: Accumulated values, KPI

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!

MVP
MVP

Re: Accumulated values, KPI

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?

MVP
MVP

Re: Accumulated values, KPI

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

markusblaut
New Contributor III

Re: Accumulated values, KPI

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

markusblaut
New Contributor III

Re: Accumulated values, KPI

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