topic Re: Table with growth rate (vs previous day) in QlikView

Table with growth rate (vs previous day)

Mon, 08 Feb 2016 16:15:34 GMT

Hello everyone,

I have a table like this :

Date_a vol

2016-02-06 20

2016-02-05 10

2016-02-04 30

And I would like to display in pivot chart:

Date_a Growth_vol

2016-02-06 +100%

2016-02-05 -66%

2016-02-04 xx

Where Growth_vol is the variation between Date_a and Date_a-1 (the previous day).

Date_a is our dimension and we wanted to use a set analysis to calculate the rate.

At first, we just wanted to display the value of "vol" for the previous day :

Date_a vol vol_1

2016-02-06 20 10

But we didn't figure out how do it, we've tried several expressions like :

sum($<{Date_a=(Date_a-1)}> vol)

and it didn't work.

Is there a way to do it with a set analysis ?

Thank you for your help

Re: Table with growth rate (vs previous day)

sunny_talwar — Mon, 08 Feb 2016 16:18:29 GMT

Try this may be:

(Sum(vol)-Below(Sum(vol)))/Below(Sum(vol))

Re: Table with growth rate (vs previous day)

MK_QSL — Mon, 08 Feb 2016 16:22:49 GMT

Like this?

Re: Table with growth rate (vs previous day)

Anonymous — Mon, 08 Feb 2016 16:23:14 GMT

try this for your Expression

=sum(vol)/below(sum(vol))

Re: Table with growth rate (vs previous day)

Anonymous69 — Mon, 08 Jan 2024 14:12:57 GMT

Hi , I wonder if there is a better way to do this ad this hasnt worked for me and my table is not a straight table as I have another column with different categories , also some dates has no value in my table , like on weekends.

Appreciate your help in advance.

Previous Day Value

Re: Table with growth rate (vs previous day)

marcus_sommer — Mon, 08 Jan 2024 15:06:10 GMT

Depending on your data-set and requirement you may simplify the matter by transferring the accumulation into the data-model and/or creating there any supporting logic, for example by generating an appropriate The As-Of Table - Qlik Community - 1466130.

If it needs to be done within the UI there is quite likely no better / easier way as using the suggested interrecord-functions. If the accumulation shouldn't happens against a single dimension else a more complex structure you will probably need to use a TOTAL within the interrecord-function and/or wrapping them with n aggr() functions. It's definitely not trivial and so I suggest to keep the chart as simple as possible.

None of the above will consider any missing data - means if there are dates not be exists you will need to populate them within the data-model to be able to use them as dimension-values in your chart.