Spread a sum over time

[#1]

Hi there,

maybe somebody of you has an idea regarding a problem of mine i want to solve in qlikview.

I want to make an report about crm data. I have an opportunity with a potential sales volume. I also have a date when we make the delivery for this opportunity. In real life thought, not all of the sales volume is delivered at once, but in smaller parts over a period of time.

So i need volume (1000), delivery Date 01.01.2010

-> 01.01 333.33

->01.02 333.333

->01.03 333.33

I found a way to do it in the script by using a for loop.

I am just wondering if there is a formula to archive the same goal ?

Your Suggestions are welcome

[#2]

Hi Fabian,

do you try to split all sales volumes over three days or how does it work?

Lukas

[fseregaza · Accepted solution]

Hi,

Is your values always divided over 3 months ? If that's the case, why don't you use the ABOVE function ?

Ex:

You use a field "D" that you want to sum and divide over time. Your dimension is, for example, the month (MonthName).

I suggest this expression:

= Rangesum( Above( Sum(D) / 3, 0, 3 ) )

Here, the Above function will calculate the Sum of every 3 preceding months (the current one, plus 2 others). It will return 3 values.

The Rangesum function will then sum these 3 values to obtain the desired delivery value.

Check if it's what you wanted.

Franck SEREGAZA

Business & Decision

[#4]

This is an interesting point, i will give it a try.

The period is not fixed to 3 Month, i was just an example, but i will try to use a variable to make a datediff between start of deleviery and expected end of delivery.

Frank, thanks for the Input, i completly forgot about rangesum 🙂