Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
fheidenstecker
Contributor II

Calculate Rolling Growth

  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

Tags (2)
9 Replies
MVP
MVP

Re: Calculate Rolling Growth

May be like in attached.

fheidenstecker
Contributor II

Re: Calculate Rolling Growth

Hi ,

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!

MVP
MVP

Re: Calculate Rolling Growth

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.

prieper
Honored Contributor II

Re: Calculate Rolling Growth

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?

fheidenstecker
Contributor II

Re: Calculate Rolling Growth

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

See also this screenshot

Growth.png !

prieper
Honored Contributor II

Re: Calculate Rolling Growth

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

prieper
Honored Contributor II

Re: Calculate Rolling Growth

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

fheidenstecker
Contributor II

Re: Calculate Rolling Growth

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

amuratbar
New Contributor III

Re: Calculate Rolling Growth

please, try this expression.

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