# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor II

## How to make a complex sum in QlikView?

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
Tags (4)
2 Replies
MVP

## Re: How to make a complex sum in QlikView?

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.

MVP

## Re: How to make a complex sum in QlikView?

Another option with a single Sum():

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