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
Did you mean:
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

May be like in attached.

Creator II

Hi ,

No, i need this calculation:

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

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

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

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

!

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

```Date_12:
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

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

Contributor III

please, try this expression.

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

Tags