topic Re: How can i calculate growth rate? in QlikView

How can i calculate growth rate?

Tue, 30 Jun 2015 09:24:46 GMT

Hi everyone,

I have time series data, saved as variable X(t), As you know it varies by time. So I want to calculate the growth rate for X(t) month by month! But it does not work in Qlikview!!

And the my time format is not compatible with Qlik time format!!

The growth rate=(X(t)-X(t-1))/X(t-1)

I would appreciate it if some one could give me some pieces of advice.

Thank you in advance

Re: How can i calculate growth rate?

sunny_talwar — Tue, 30 Jun 2015 11:25:50 GMT

Are you trying to do this on the front end or the script? if in the script you will need to use the Peek or Previous function and if you are trying to do in front end, you can achieve the same by using RangeSum(Above() or Below()) function. For more help on this topic, you will have to share more information.

Best,

Sunny

Re: How can i calculate growth rate?

Tue, 30 Jun 2015 12:18:33 GMT

Hi,

I hope it will help u to complete the task

lo:

LOAD * Inline [

sl.no,name,salary

01,balu,16646

02,bif,61646

03,AK,111111

];

LOAD *,

((salary-Previous(salary))/Previous(salary))*100 as %growth

Resident lo;

Thank you

balaji.k

Re: How can i calculate growth rate?

avinashelite — Tue, 30 Jun 2015 12:22:34 GMT

can you please share the sample data because you have mentioned it includes date issue also

Re: How can i calculate growth rate?

Tue, 30 Jun 2015 12:30:10 GMT

Dear Sunny,

Thank you for replying.

Yes, I used the rowno () (as date format dos not match the Qlik date format), above() and below() functions in front end. do the peek and previous work when I use the rowno () function?

And I wonder if you can let me know what is the difference between sum and rangesum.

best regards,

Zahra

Re: How can i calculate growth rate?

sunny_talwar — Tue, 30 Jun 2015 12:35:16 GMT

You can find the difference between Sum() and RangeSum if you see gwassenaar‌ comments on this thread: Re: what is the range of sum function ?‌

Re: How can i calculate growth rate?

sunny_talwar — Tue, 30 Jun 2015 12:36:51 GMT

Not sure I completely understand your following question:

do the peek and previous work when I use the rowno () function?

Re: How can i calculate growth rate?

Tue, 30 Jun 2015 12:39:57 GMT

Dear balaji k,

Thank you very much but it works when data is sorted or there is some kind of ranking. Am I true?

In another word, can I use the rowno() function in script?

best,

Zahra

Re: How can i calculate growth rate?

Tue, 30 Jun 2015 12:52:48 GMT

Dear Avinash R,

Thank you, Yes of course.

as usual numbers 1-12 are the months and 93 , 92 are the years but the problem is that the months do not matches the Qlikview Date and calender.

t X(t)

92/01	71565187
92/02	88710966
92/03	88138813
92/04	92719741
92/05	95968043
92/06	102555007
92/07	98895752
92/08	92763772
92/09	93568153
92/10	94379721
92/11	105969263
92/12	125155154
93/01	93854421

Re: How can i calculate growth rate?

sunny_talwar — Tue, 30 Jun 2015 13:07:59 GMT

Is this what you are trying to get?

Script:

Table:

LOAD RowNo() as Sort,

LOAD * Inline [

Time, Value

92/01, 71565187

92/02, 88710966

92/03, 88138813

92/04, 92719741

92/05, 95968043

92/06, 102555007

92/07, 98895752

92/08, 92763772

92/09, 93568153

92/10, 94379721

92/11, 105969263

92/12, 125155154

93/01, 93854421

];

Created a straight table with Time as dimension and three expressions:

1) =Sum(Value)

2) =Above(Sum(Value))

3) =Sum(Value)/Above(Sum(Value))-1

HTH

Best,

Sunny

Re: How can i calculate growth rate?

Sat, 04 Jul 2015 06:30:43 GMT

Hi and thank you.

yes I want to create a gauge chart to show the growth rate that you calculate.

best regards,

Zahra

Re: How can i calculate growth rate?

sunny_talwar — Sat, 04 Jul 2015 12:02:35 GMT

Do you want to see the growth rate for the most recent period on a gauge chart? Is that what you requirement is?

Best,

Sunny

Re: How can i calculate growth rate?

sunny_talwar — Sat, 04 Jul 2015 15:41:50 GMT

Trying to get this?

PFA the sample file.

Best,

Sunny

Re: How can i calculate growth rate?

Sun, 05 Jul 2015 08:19:39 GMT

Dear Sunny

thank you for your kind help.

I tried to make the gauge, but it didn't work.

I think I should use If() and rowno() functions, but It shows the 0 in the gauge!

best regards,

Zahra

Re: How can i calculate growth rate?

sunny_talwar — Sun, 05 Jul 2015 11:48:23 GMT

What numbers are you trying to show in your gauge chart? Until and unless I know what your expected output needs to be, I won't be able to help.

Best,

Sunny

Re: How can i calculate growth rate?

Mon, 06 Jul 2015 05:37:05 GMT

Hi and thanks a lot,

I want to show the value growth rate (month by month) in gauge chart. I want to see the monthly value growth rate in the gauge, when I filter the date. for example when I select the year 92 and the month 3, it shows -.64% and when It isn't filtered, the gauge show the last growth rate data (-.25%). And the year and the month are separated in two different columns.

regards,

Zahra

Re: How can i calculate growth rate?

sunny_talwar — Mon, 06 Jul 2015 10:34:58 GMT

Here you go:

When selection is made (Year = 92 and Month 03)

When no selection made

Attaching the qvw for your reference.

HTH

Best,

Sunny

Re: How can i calculate growth rate?

Tue, 07 Jul 2015 04:27:26 GMT

Dear Sunny,

I defined some variables to made such gauge! but wonderfully you made this very easily and simply! That is great!

I really appreciate all the help you gave me.

Best Regards,

Zahra

Re: How can i calculate growth rate?

sunny_talwar — Tue, 07 Jul 2015 09:14:39 GMT

Not a problem

I am glad I was able to help. I would ask you, that if you got the answer you were please mark the correct answer as well any helpful answers to close the thread.

Best,

Sunny

Re: How can i calculate growth rate?

Mon, 13 Jul 2015 05:01:40 GMT

Dear Sunny,

I have another problem in calculating growth rate year by year, It means that for example (value of (year 93 and month 3) / value of (year 92 and month 3))-1.

I did this using defining some variables but I would appreciate it if you let me know that if there is a way to calculate this without defining any variable.

Thank you in advance,

Zahra