Calculated Dimension based on Year

[gowtham_patnaik]

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)

[sunny_talwar]

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

[sdmech81]

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

[gowtham_patnaik]

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))

[gowtham_patnaik]

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

[gowtham_patnaik]

i am calculating KPI as calculated dimension.

[sunny_talwar · Accepted solution]

You can try this:

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

[gowtham_patnaik]

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

[gowtham_patnaik]

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