calculating percentage of previous rows data compared to current row

[#1]

Hi,

Looking to calculate the percentage growth from day to day and I am having trouble finding the correct function. I have tried the above function but can't get it to work. example data below

Date     user number     growth %

11/7     10                       -

12/7     20                    100%

13/7     25                    25%

Your help would be much appreciated

Thanks

Michael

[#2]

If user number is sum(users) and supposing you only have one dimension (Date) and always is data on every day, the growth column would be:

num((sum(users)/above(Sum(users)))-1,'#.###,00%')

[kumarnatarajan]

Hi,

Try the syntax.

=Num((Sum(Amount)-above(Amount))/above(Amount),'#,##0%')

find the attached file.

[#4]

Hi Michael,

The above function must solve it if you want to use a chart - it's a chart inter record function.

The peek function may also be interesting to you - it's an inter record function.

peek( fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the input table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

Examples:

peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current input table.

peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current input table.

peek( 'Sales', 0, Tab1 )
returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.

Hope it helps!

[#5]

The peek function doesn't appear to be recognized in Qlikview. It returns blank for the way you are suggesting. peek('user number') is blank in this case.

I am looking to do the calculation in the table as i included in my first post and not in a chart.

any help would be appreciated

Thanks

Michael

[#6]

Hi Michael, sorry about the delay!

Forget the peek() function, as there is a lot easier way to do this. In the script, when loading your data, you just need to create a calculated field using the previous() function.

Something like this:

              Load amount,

                      date,

                      (amount-previous(amount))/previous(amount) as growth

               ...

Hope it works for you.