8 Replies Latest reply: Feb 2, 2017 1:59 AM by Gowthamatnaik Basangi

# Calculated Dimension based on Year

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

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

• ###### Re: Calculated Dimension based on Year

i am calculating KPI as calculated dimension.

• ###### Re: Calculated Dimension based on Year

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

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

• ###### Re: Calculated Dimension based on Year

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

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

=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

i think i am almost done.