Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Spread a sum over time

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

1 Solution

Accepted Solutions
fseregaza
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

3 Replies
Not applicable
Author

Hi Fabian,

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

Lukas

fseregaza
Partner - Contributor III
Partner - Contributor III

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

Not applicable
Author

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 🙂