Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
arjanijlenhave
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 contract1-4-2015
DateAmount paidAmount to be paid
1-1-2015100-100
1-2-2015100-100
1-3-2015100-100
1-4-20151001100
1-5-20151001000
1-6-2015100900
1-7-2015100800
1-8-2015100700
1-9-2015100600
1-10-2015100500
1-11-2015100400
1-12-2015100300
2 Replies
MVP
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
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])