# 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.

New Contributor III

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

1 Solution

Accepted Solutions MVP

## 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 MVP

## Re: Calculated Dimension based on Year

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

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

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

New Contributor III

## Re: Calculated Dimension based on Year

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

New Contributor III

## Re: Calculated Dimension based on Year

i am calculating KPI as calculated dimension. MVP

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

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

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

Community Browser