Calculate Rolling Growth

[fheidenstecker]

  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

[tresesco]

May be like in attached.

[fheidenstecker]

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!

[tresesco]

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]

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]

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

See also this screenshot

!

[prieper]

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]

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]

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

[Anonymous]

please, try this expression.

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