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

    Calculate max balance for each customer/year.

    Giuseppe Gallina

      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.