How to make a complex sum in QlikView?

[Anonymous]

Hi Community,

We are struggling to find a solution for our problem, and hope one off you can help.

In our project we need to properly calculate the 'Amount to be paid' within user based time selections.

Based on the table below (see below signature), I have created several use cases for better understanding.

Use case 1: The user makes a time selection of 1-1-2015 up to and including 1-3-2015

- In this specific use case we expect that the most recent 'amount to be paid' (the -100 of 1-3-2015)  is summed with the 'amounts to be paid' of the remaining records in the selection (-100 of 1-1-2015 and -100 of 1-2-2015), leading to a result of -300.

Use case 2: The user makes a time selection of 1-1-2015 up to and including 1-6-2015

-  In this specific use case we expect that the most recent 'amount to be paid' (the 900 of 1-6-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection (-100 + -100 + -100), leading to an overall result of 600 (= 900 + -300).

Use case 3: The user makes a time selection of 1-1-2015 up to and including 31-12-2015

-  In this specific use case we expect that the most recent 'amount to be paid' (the 300 of 1-12-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection (-100 + -100 + -100), leading to an overall result of 0 (= 300 + -300).

Use case 4: The user makes a time selection of 1-2-2015 up to and including 31-12-2015

-  In this specific use case we expect that the most recent 'amount to be paid' (the 300 of 1-12-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection  -100 + -100), leading to an overall result of 100 (= 300 + -200).

Use case 5: The user makes a time selection of 1-5-2015 up to and including 31-5-2015

-  In this specific use case we expect that the most recent 'amount to be paid' (the1000 of 1-5-2015 ) is the result the selection

Use case 6: The user makes a time selection of 1-5-2015 up to and including 31-6-2015

-  In this specific use case we expect that the most recent 'amount to be paid' (the 900 of 1-6-2015 ) is the result the selection

Kind regards,

Arjan IJlenhave 

Date renewal contract	1-4-2015
Date	Amount paid	Amount to be paid
1-1-2015	100	-100
1-2-2015	100	-100
1-3-2015	100	-100
1-4-2015	100	1100
1-5-2015	100	1000
1-6-2015	100	900
1-7-2015	100	800
1-8-2015	100	700
1-9-2015	100	600
1-10-2015	100	500
1-11-2015	100	400
1-12-2015	100	300

[swuehl]

Maybe something like

=Rangesum(

If(Max(Date) >= '1-4-2015',FirstSortedValue([Amount to be paid], -Date)),

Sum({<Date *= {"<1-4-2015"}>} [Amount to be paid])

)

Where constant '1-4-2015' is your renewal date and can be replaced with a variable or data record lookup.

[swuehl]

Another option with a single Sum():

= Sum({<Date = (Date*{"<1-4-2015"})+{"$(=Date(Max(Date)))"}>} [Amount to be paid])