topic Re: Calculated Dimension based on Year in QlikView

Calculated Dimension based on Year

gowtham_patnaik — Mon, 26 Jan 2026 16:26:21 GMT

Hi guys,

I am trying to calculate KPI formula in calculated dimension.

My table is:

Year	Company	Income	Asset	Data
2012	a	16	20	10
2013	a	20	15	20
2014	a	25	13	10
2015	a	13	10	15

My KPI calculation formula is:

Income(2015) Income(2014) Income(2013)

CalDim = -------------------------------- + -------------------------------- + --------------------------------

Asset(2015)+Asset(2014) Asset(2014)+Asset(2013) Asset(2013)+Asset(2012)

Re: Calculated Dimension based on Year

sunny_talwar — Wed, 01 Feb 2017 12:38:29 GMT

Is this a KPI or calculated dimension? You have me confused?

Re: Calculated Dimension based on Year

sdmech81 — Wed, 01 Feb 2017 12:47:07 GMT

May be like this

calcDim= sum({<year={'2015'}>}Income)/(sum({<year={'2015'}>}Asset)/sum({<year={'2014'}>}asset))

repeat for 2014 and 2103 adding them..Try

Re: Calculated Dimension based on Year

gowtham_patnaik — Wed, 01 Feb 2017 13:23:21 GMT

Hi guys,

I am trying to calculate KPI formula in calculated dimension.

I had used input box for Last Year and Period (From Year will be calculated based on Last Year and Period) and it is working fine.

But i am unable to calculate KPI based on year provided by user i,e,

My table is:

Year Company Income Asset Data

2012 a 16 20 10

2013 a 20 15 20

2014 a 25 13 10

2015 a 13 10 15

Expected KPI calculation formula is: (Last Year= 2015, Period= 2)

Income(of year 2015) Income(2014) Income(2013)

CalDim(2015-2013) = -------------------------------- + -------------------------------- + ------------------------------

Asset(2015)+Asset(2014) Asset(2014)+Asset(2013) Asset(2013)+Asset(2012)

My KPI calculation is like: (Last Year= 2015, Period= 2)

Income(of year 2015) Income(2014) Income(2013)

CalDim(2015-2013) = -------------------------------- + -------------------------------- + --------------------------------

Asset(2015)+Asset(2014) Asset(2015)+Asset(2014)Asset(2015)+Asset(2014)

Set Expression i had used is:

=Num(aggr(Sum({$< Company = {'*'}>}{$< Year = {">=$(vFromFy) <=$(vLastFy) "}>}Income /

Num(aggr(Sum({$< Company= {'*'}>}{$< Year = {"$(vLastFy)","=$(vLastFy)-1"}>} Asset),Company))),Company))

Re: Calculated Dimension based on Year

gowtham_patnaik — Wed, 01 Feb 2017 13:24:15 GMT

I just want to calculate dynamically based on the year given by user.

Re: Calculated Dimension based on Year

gowtham_patnaik — Wed, 01 Feb 2017 13:25:27 GMT

i am calculating KPI as calculated dimension.

Re: Calculated Dimension based on Year

sunny_talwar — Wed, 01 Feb 2017 14:07:37 GMT

You can try this:

=FirstSortedValue(Aggr(RangeSum(Above(Sum(Income)/RangeSum(Above(Sum(Asset), 0, 2)), 0, 3)), Year), -Year)

Re: Calculated Dimension based on Year

gowtham_patnaik — Thu, 02 Feb 2017 06:58:26 GMT

=num(Aggr(Above(sum({$< Year = {">=$(vFromYr) <=$(vLastYr) "}>} Income)/RangeSum(Above(sum({$< Year = {">=$(vFromYr) <=$(vLastYr) "}>} Asset), 0, 2)), 0, 1), Year))

Re: Calculated Dimension based on Year

gowtham_patnaik — Thu, 02 Feb 2017 06:59:54 GMT

i think i am almost done.

i had used below expression:

=num(Aggr(Above(sum({$< Year = {">=$(vFromYr) <=$(vLastYr) "}>} Income)/RangeSum(Above(sum({$< Year = {">=$(vFromYr) <=$(vLastYr) "}>} Asset), 0, 2)), 0, 1), Year))

Thank you