Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator 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

9 Replies
MVP
MVP

May be like in attached.

Creator II
Creator II

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

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.

Master II
Master II

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?

Creator II
Creator II

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

See also this screenshot

Growth.png !

Master II
Master II

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

Master II
Master II

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

Creator II
Creator II

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

Contributor III
Contributor III

please, try this expression.

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