
Re: Expression: Calculate a 'compound' sum over a number of weeks aggregated over a couple of months
Stefan Wühl Nov 25, 2011 7:33 AM (in response to Ross Pudney )Hi,
I must admit that I haven't fully understood this uplift thing.
Maybe you could use chart inter record functions to achieve what you want, e.g.
=rangesum(above(count(distinct WeekCommence),0,RowNo()))
as expression in your table will get you the cummulative sum of of weeks per Month.
So maybe you are looking for something like:
=Forecast_Sales+sum(Planned_Sales)*(Avg_Uplift*rangesum(above(count(distinct WeekCommence),0,RowNo())))
or
=Forecast_Sales+rangesum(above(sum(Planned_Sales),0,RowNo()))
*(Avg_Uplift*rangesum(above(count(distinct WeekCommence),0,RowNo())))
I don't know...

Expression: Calculate a 'compound' sum over a number of weeks aggregated over a couple of months
Ross Pudney Nov 25, 2011 5:33 AM (in response to Stefan Wühl )Thanks this worked great. Can you explain to me what the rangesum and above function do together please?

Expression: Calculate a 'compound' sum over a number of weeks aggregated over a couple of months
Stefan Wühl Nov 25, 2011 11:21 AM (in response to Ross Pudney )The rangesum(argument1, argument2, ...) function will return the sum of its arguments. As opposed to the + operator, rangesum will treat all nonnumeric values as 0 (so for example if one argument evaluates to NULL, it will add as zero to the sum, it will not render the complete sum as NULL).
above(expression) function will essentially returns the value of its expression evaluated with the chart's dimension values as they appear on the row above the current row. So if you use a chart with dimension month,
an expression like above(sum(Value)) will return the value as it would for month in the row above, e.g. in row for Month February, it will return the sum(Value) of Month January (if Januar is located / sorted above February).
You could specify the offset (number of rows to look above) as second parameter (default = 1). If you set it to zero, it will use the current row.
By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table rows counting upwards from the original cell. In this form, the function can be used as an argument to any of the special range functions, like the rangesum() function.
Using the rowno() for that and second parameter 0, we create a range of values created by evaluating the expression from the current row up to the beginning of the table (taking all preceding rows into account for the rangesum() function)).
Hope this makes it clear,
Stefan

