12 Replies Latest reply: Mar 8, 2015 1:59 PM by Giuseppe Gallina

# 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 Code Year Date Amount 1 2013 14/01/2013 1.500,00 1 2014 20/03/2014 -500,00 1 2014 14/03/2014 200,00 1 2014 14/03/2014 -300,00 2 2013 18/07/2013 1.000,00 2 2013 20/09/2013 -700,00 2 2013 20/09/2013 800,00 2 2014 19/05/2014 200,00 2 2014 30/11/2014 -1.000,00 3 2013 21/10/2013 8.000,00 3 2014 31/07/2014 -7.000,00 3 2015 15/01/2015 -1.000,00

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

 Customer Code Year Date Balance for Customer/Date 1 2013 14/01/2013 1.500,00 1 2014 14/03/2014 2.100,00 1 2014 20/03/2014 1.000,00 2 2013 18/07/2013 1.000,00 2 2013 20/09/2013 1.400,00 2 2014 19/05/2014 1.300,00 2 2014 30/11/2014 300,00 3 2013 21/10/2013 8.000,00 3 2014 31/07/2014 1.000,00 3 2015 15/01/2015 0,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 Code Year Date Max Balance for Customer/Year 1 2013 14/01/2013 1.500,00 1 2014 14/03/2014 2.100,00 2 2013 20/09/2013 1.400,00 2 2014 19/05/2014 1.300,00 3 2013 21/10/2013 8.000,00 3 2014 31/07/2014 1.000,00 3 2015 15/01/2015 0,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.

Thank you very much.

• ###### Re: Calculate max balance for each customer/year.

try the attachment

• ###### Re: Calculate max balance for each customer/year.

Thank you Massimo, now I try!

• ###### Re: Calculate max balance for each customer/year.

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!

• ###### Re: Calculate max balance for each customer/year.

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

• ###### Re: Calculate max balance for each customer/year.

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.

• ###### Re: Calculate max balance for each customer/year.

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?

• ###### Re: Calculate max balance for each customer/year.

Have you tried putting in DISTINCT ...

FirstSortedValue(DISTINCT Date, -Balance)

• ###### Re: Calculate max balance for each customer/year.

Yes!!!

It work!

I tried with Distinct but Qlikview say: "Error in expression"

Now It work!

Thank you very much Petter!

Om du pratar på svenska: Tack så mycket! :-)

• ###### Re: Calculate max balance for each customer/year.

I think you can play with the second parameter

• ###### Re: Calculate max balance for each customer/year.

Yes Massimo, good idea, this work also!!!.

Thank you very much!

... Grazie mille! :-)

• ###### Re: Calculate max balance for each customer/year.

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.

• ###### Re: Calculate max balance for each customer/year.

Thank you Petter, now I try!