

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be like in attached.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi there, i attached an excel File ,what i excpet.
See also this screenshot
!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I precalculated the values in the script. Not the most elegnant solution 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
please, try this expression.
=1-(RangeSum(Above(Sum(Value),0,12))/RangeSum(Above(Sum(Value),12,12)))
