topic Re: Dynamic Growth Rate? in QlikView

Dynamic Growth Rate?

Tue, 13 May 2014 18:44:37 GMT

I'm trying to calculate a growth rate between the two user-selected endpoints of my data, but I can't figure out how to make the formula work. For instance, if the user selects 2013-2016, I'd like to display the growth rate for that period. Similarly, if they select 2010-2019, I'd like to display the growth rate for that period as well.

Current variables are [FiscalYear] and [Amount]

Re: Dynamic Growth Rate?

maxgro — Tue, 13 May 2014 19:14:44 GMT

are the end point years?

maybe growth in % / number of years?

( sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount) - sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount) )

( sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount) * (Max([Fiscal Year]) -Min([Fiscal Year]))

Re: Dynamic Growth Rate?

Tue, 13 May 2014 19:21:32 GMT

The end points should be the first and last years within the selected years in [FiscalYear] - if the user selects 14-19, it should be 14 and 19. And yes, it should be % growth over the user selected period (so % growth from 14-19 in the aforementioned example).

Re: Dynamic Growth Rate?

maxgro — Tue, 13 May 2014 19:31:17 GMT

ok, thanks, my idea is above, hope it helps you

Re: Dynamic Growth Rate?

Tue, 13 May 2014 19:37:03 GMT

I entered your suggestion (had to close the parentheses at the end) but it's just giving me null values. Any idea?

Re: Dynamic Growth Rate?

maxgro — Tue, 13 May 2014 19:44:12 GMT

if you think the idea is ok (from the logical point of view) you can start to check an expression

sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount)

as there are 3 similar expression, when the first one is ok, the other too should be ok

assuming you select the period 2013-2016, previous expression should be the same (same value) as

sum( {$<[Fiscal Year]= {2016}>} Amount)

Re: Dynamic Growth Rate?

Tue, 13 May 2014 19:52:25 GMT

When I just enter one section of the formula with 2014-2017 selected:

(sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount)

(sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount)

It returns the same value - the sum total of 2014-2017. Any ideas?

Re: Re: Dynamic Growth Rate?

maxgro — Tue, 13 May 2014 20:22:23 GMT

just tried in the attachment with some test data

Re: Dynamic Growth Rate?

Tue, 13 May 2014 20:42:30 GMT

EDIT: Figured it out - the issue was on my end. Your solution worked beautifully. Thank you!