Qlik Community

QlikView Integration

Discussion Board for collaboration on QlikView Integration.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gowtham_patnaik
New Contributor III

Calculated Dimension based on Year

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)

1 Solution

Accepted Solutions

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)

8 Replies

Re: Calculated Dimension based on Year

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

sdmech81
Valued Contributor

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

gowtham_patnaik
New Contributor III

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

gowtham_patnaik
New Contributor III

Re: Calculated Dimension based on Year

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

gowtham_patnaik
New Contributor III

Re: Calculated Dimension based on Year

i am calculating KPI as calculated dimension.

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)

gowtham_patnaik
New Contributor III

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

gowtham_patnaik
New Contributor III

Re: Calculated Dimension based on Year

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