Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ggallina
Contributor III

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
MVP
MVP

Re: Calculate max balance for each customer/year.

try the attachment

1.jpg

12 Replies
MVP
MVP

Re: Calculate max balance for each customer/year.

try the attachment

1.jpg

MVP
MVP

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.

2015-03-08 Balance.png

ggallina
Contributor III

Re: Calculate max balance for each customer/year.

Thank you Massimo, now I try!

ggallina
Contributor III

Re: Calculate max balance for each customer/year.

Thank you Petter, now I try!

ggallina
Contributor III

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!

MVP
MVP

Re: Calculate max balance for each customer/year.

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

ggallina
Contributor III

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.

Please can you help me?

ggallina
Contributor III

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?

MVP
MVP

Re: Calculate max balance for each customer/year.

Have you tried putting in DISTINCT ...

     FirstSortedValue(DISTINCT Date, -Balance)

Community Browser