Expression: Calculate a 'compound' sum over a number of weeks aggregated over a couple of months

[#1]

Hi All,

I have a challenge that I am unable to solve and I wondered if someone out there is able to help. I am being asked to implement a simple Qlikview Dashboard to replace a manually created Excel spreadsheet and I have come across a particularly strange 'business' logic which I need to try and replicate.

The background:

I have a table of data (mocked up) that has forecasted sales data per week for a particular department, a lookup table with the average uplift for that department and planned sales data. I know that on average across the year, this department experiences a 3% uplift per week in the the forecasted sales as new sales are made and/or opportunities for sales are identified. The assumption to start with is that I am calculating this data a couple of weeks before the start of the first week in my data table (as it is forecasting)

The challenge:

For reasons not clear to me, we calculate the value of the uplift from our planned data (so in my example, I would calculate 3% of £2000) per week, aggregate that up to a month (we use a 4-4-5 system) and then add that to the monthly forecast sales figure to give us a Forecast + Uplift value.

The above calculations are obviously easy to do, however, the challenge I have is to replicate the next bit of 'business logic' which is used in the current Excel spreadsheet (not supplied), which is:

To get an 'accurate' Forecast + Uplift figure, we need to sum the total number of uplifts across all weeks for all the relevant months involved.

So for example, for January, I would only want to sum the total number of uplifts per week which are in January as that is the first month (4 weeks worth). For February, I would want to sum the total uplift for each week in January AND February (8 weeks worth). In March, I would want to sum the total uplift for January, February and March (13 weeks worth).

What I wanted to know therefore is:

1) Is this possible?

2) How could it be done if so?

Thanks for your help in advance.

RG

[swuehl · Accepted solution]

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...

[#3]

Thanks this worked great. Can you explain to me what the rangesum and above function do together please?

[swuehl]

The rangesum(argument1, argument2, ...) function will return the sum of its arguments. As opposed to the + operator, rangesum will treat all non-numeric 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