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.
