Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
Please can you help me?
Thank you very much.
try the attachment
try the attachment
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.
Thank you Massimo, now I try!
Thank you Petter, now I try!
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!
So you will have to decide for QlikView then... use Min(Date) or Max(Date) ...
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?
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?
Have you tried putting in DISTINCT ...
FirstSortedValue(DISTINCT Date, -Balance)