topic Re: Calculate Rolling Growth in QlikView

Calculate Rolling Growth

fheidenstecker — Thu, 20 Sep 2018 08:28:12 GMT

Hi there,

i want to create a line chart to show the growth of the last 12 Month compared to the last 12 Month in the year before. I created an Expression, which gives me the right result. But when i put it into a chart, it gives me some strange numbers. For July i would expect a growth of -2,35% in the chart, but it shows me -1,21%

I guess i have to do some Rolling Calculation. I tried some of the rangesum/above logic, but no success

Can anybody help me?

I have attached an example

Cheeers From Germany,

Fabian

Re: Calculate Rolling Growth

tresB — Thu, 20 Sep 2018 08:56:50 GMT

May be like in attached.

Re: Calculate Rolling Growth

fheidenstecker — Thu, 20 Sep 2018 09:06:02 GMT

Hi tresesco,

No, i need this calculation:

sum({<Year=,Month=,Date={">$(=addmonths(max(Date),-12))<=$(=max(Date))"}>}Value)/

sum({<Year=,Month=,Date={">$(=addmonths(max(Date),-24))<=$(=addmonths(max(Date),-12))"}>}Value)-1

So the logic is last 12 Month, compared to the 12 Month before. It has to be rolling.

I need this rolling calculation in a chart to be done for every month!

Re: Calculate Rolling Growth

tresB — Thu, 20 Sep 2018 09:12:24 GMT

Help me understand what is wrong in the figures (or in the chart behavior) you get in the chart and what you expect with few examples.

Re: Calculate Rolling Growth

prieper — Thu, 20 Sep 2018 09:55:08 GMT

Currently you compare month by month, i.e. July 2018 vs July 2017.

Do you need to compare Jan-Jul 2018 vs same period 2017?

Re: Calculate Rolling Growth

fheidenstecker — Thu, 20 Sep 2018 11:18:25 GMT

Hi there, i attached an excel File ,what i excpet.

Re: Calculate Rolling Growth

prieper — Thu, 20 Sep 2018 14:48:02 GMT

Easiest might be to create a field "RollingMonth" in the script with formula

Year * 12 + Month

And then use an expression like

(Sum({<Year =, Month =, RollingMonth = {">= $(=MAX(RollingMonth) -11) <= $(=MAX(RollingMonth) )" } >}Value)

Sum({<Year =, Month =, RollingMonth = {">= $(=MAX(RollingMonth) -23) <= $(=MAX(RollingMonth) -12 )" } >}Value)

) -1

edit: above deleted - was no related to OPs question

HTH Peter

Re: Calculate Rolling Growth

prieper — Fri, 21 Sep 2018 08:53:48 GMT

Add an As-Of-Calendar to your script:

Date_12:
    LOAD DISTINCT 
        Date                                     AS Date_12,
        YEAR(Date) * 12 + MONTH(Date)            AS Date_12_rollingMonth,        // easier for SET-calculations
        DATE(ADDMONTHS(Date, -ITERNO() +1))        AS Date
    RESIDENT
        Data
    WHILE
        ITERNO() <= 12;

RIGHT JOIN (Date_12) LOAD Date RESIDENT Data;                                    // Just to align with the existing Dates

And then use an expression like

(SUM(Value)
/
SUM({<
    Date_12 =,  
    Date_12_rollingMonth = {">=$(=MIN(Date_12_rollingMonth) -12) <=$(=MAX(Date_12_rollingMonth) -12)"}
    >} Value)
)
-1

Dimension then should be MONTH(Date_12).

Selection to be done in the filed "Date_12".

HTH

Peter

Re: Calculate Rolling Growth

fheidenstecker — Wed, 26 Sep 2018 13:14:21 GMT

I precalculated the values in the script. Not the most elegnant solution 😞

Re: Calculate Rolling Growth

Anonymous — Thu, 27 Sep 2018 11:57:00 GMT

please, try this expression.

=1-(RangeSum(Above(Sum(Value),0,12))/RangeSum(Above(Sum(Value),12,12)))