2 Replies Latest reply: Mar 10, 2017 9:26 AM by Jens Leysen RSS

    Last 5 records from Each Month

    Brandon Golden

      I'm trying to get a sum in Qlik Sense that will be displayed in a bar chart

      I need to show, by month, the last 5 records in the data based on each month (but not necessary from that month). I'm thinking it would need to include a combination of an aggr and recno but that's a guess.

       

      For example:

       

      DateMonthIDAmount
      1-JanJanuary110
      12-JanJanuary220
      13-JanJanuary330
      18-JanJanuary440
      19-JanJanuary550
      29-JanJanuary660
      11-FebFebruary770
      12-FebFebruary880
      17-FebFebruary990
      1-MarMarch10100
      3-MarMarch11110
      8-MarMarch12120
      9-MarMarch13130

      1-Apr         April

       

      January would be the sum of ID 6,5,4,3,2

      February would be the sum of ID 9,8,7,6,5

      March would be the sum of ID 13,12,10,9

      April would also be the sum of ID 13,12,11,10,9

        • Re: Last 5 records from Each Month
          Andrey Khoronenko

          Hi Brandon,

           

          It has become interesting to try to solve your task. I propose to solve the task using the data island, transferring all the calculations to the script. In the attached file QVF, as an variant, an implementation example. All comments are in script code.

          I will be glad if I could help you.

           

          Regards,

          Andrey

          • Re: Last 5 records from Each Month
            Jens Leysen

            Hey Brandon,

             

            In this case your ID keeps going up together with the date. When you select a date/month, you can use MAX(ID) to find out what your highest ID is or better said, your starting point. At this point you can add the 4 ID's previous to this point and add them to your MAX(ID) and you will have the 5 highest ID's.

             

            Hope this works.