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

    Calculated Dimension based on Year

    Gowthamatnaik Basangi

      Hi guys,

       

      I am trying to calculate KPI formula in calculated dimension.

      My table is:

          

      YearCompanyIncomeAssetData
      2012a162010
      2013a201520
      2014a251310
      2015a131015


      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

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

          • Re: Calculated Dimension based on Year
            Sachin duganavar

            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
              Gowthamatnaik Basangi

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