Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like in attached.
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!
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.
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?
Hi there, i attached an excel File ,what i excpet.
See also this screenshot
!
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
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
I precalculated the values in the script. Not the most elegnant solution 😞
please, try this expression.
=1-(RangeSum(Above(Sum(Value),0,12))/RangeSum(Above(Sum(Value),12,12)))