Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate max balance for each customer/year.

Good morning,

I have a question about calculating the max of balance fro each customer/year from a database that contains:

- customer code;

- year;

- date;

- amount of this date.

If I create this with a programation language it isn't very difficult but I dont know if I can do this with Qlikview Scripting and Qlikview Chart.

Far example my database is this:

Customer CodeYearDateAmount
1201314/01/20131.500,00
1201420/03/2014-500,00
1201414/03/2014200,00
1201414/03/2014-300,00
2201318/07/20131.000,00
2201320/09/2013-700,00
2201320/09/2013800,00
2201419/05/2014200,00
2201430/11/2014-1.000,00
3201321/10/20138.000,00
3201431/07/2014-7.000,00
3201515/01/2015-1.000,00

So, I need to calculate the balance of every customer and day:

Customer CodeYearDateBalance for Customer/Date
1201314/01/20131.500,00
1201414/03/20142.100,00
1201420/03/20141.000,00
2201318/07/20131.000,00
2201320/09/20131.400,00
2201419/05/20141.300,00
2201430/11/2014300,00
3201321/10/20138.000,00
3201431/07/20141.000,00
3201515/01/20150,00

And the I need to have what I need, the max balance for every Customer/Year and the date of the max balance amount (I can do a chart or a table wiht this in QlikView):

Customer CodeYearDateMax Balance for Customer/Year
1201314/01/20131.500,00
1201414/03/20142.100,00
2201320/09/20131.400,00
2201419/05/20141.300,00
3201321/10/20138.000,00
3201431/07/20141.000,00
3201515/01/20150,00

I can do this with Java or another Language and have the last table but I want to know if I can do this only with QlikView.

Please can you help me?

Thank you very much.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

try the attachment

1.jpg

View solution in original post

12 Replies
maxgro
MVP
MVP

try the attachment

1.jpg

petter
Partner - Champion III
Partner - Champion III

I have attached two different approaches depending on whether you want to use a straight table (simplest) or a pivot table.

It is also possible to do this in the Load Script in a simple manner by accumulating while you load the transaction

rows in order by ascending date.

2015-03-08 Balance.png

Anonymous
Not applicable
Author

Thank you Massimo, now I try!

Anonymous
Not applicable
Author

Thank you Petter, now I try!

Anonymous
Not applicable
Author

Thank you very much Massimo, it work!!!

I must study QlikView for many time, I will do a course next week!

I have only a problem, when I have the same max Balance in a Year with two or more different date I dont have a date in the column date, I think QilkView have a problem to "decide" wich date can QlikView use!

petter
Partner - Champion III
Partner - Champion III

So you will have to decide for QlikView then... use Min(Date) or Max(Date) ...

Anonymous
Not applicable
Author

Yes Petter, you are right, but I dont know which is the formula for the date for the line with the max balance  if the max balance have two or more different date in a year for a customer.

I use the formula that Massimo suggest

Date(FirstSortedValue(Date, -Balance))

and this work fine if I dont have for a client two different date with the same max saldo.

For example:

Customer 1 Year 2014

Date                             Balance

01/03/2014                   1.000,00

05/05/2014                      500,00

15/09/2014                   1.000,00

In the table I have the right Balance but not the date and I try with max but dont work with FirstSortedValue.

Please can you help me?

Anonymous
Not applicable
Author

Mmmmhhh, I'm reading in the documentation that if FirstSortedValue find more than one value the function return null.

I dont know what can I do, in this case.

And why function return null?

petter
Partner - Champion III
Partner - Champion III

Have you tried putting in DISTINCT ...

     FirstSortedValue(DISTINCT Date, -Balance)