4 Replies Latest reply: Jan 23, 2017 8:36 AM by Andrey Khoronenko RSS

    Cummulating in Scrip with one Month value missing

    Manish Kumar

      Hi

       

      I have a data with some valued on monthly basis. But for one month value is not there.

      Thus when I cummulate the data in script it omits the row for the month which is not having the data. I need the cummulative value for that month also which is not having the value itself.

       

      Original data

       

      MonthValues
      01-04-201580615
      01-05-201574193
      01-06-201574193
      01-07-201574193
      01-08-201574193
      01-09-201574193
      01-10-201578336
      01-11-201574193
      01-12-201574193
      01-01-201674893
      01-03-2016

      8357

       

      Desired result

        

      MonthCummuative Values
      01-04-201580615
      01-05-2015154808
      01-06-2015229001
      01-07-2015303194
      01-08-2015377387
      01-09-2015451580
      01-10-2015529916
      01-11-2015604109
      01-12-2015678302
      01-01-2016753195
      01-02-2016753195
      01-03-2016761552
        • Re: Cummulating in Scrip with one Month value missing
          Anil Babu

          May be using, Full accumulation for Sum(Values) in expression Tab of Straight table, For ref please follow the image

          Capture.PNG

           

          Or May be expression

          Rangesum(Below(Sum(Values),0),Above(Values,1,1000))

          • Re: Cummulating in Scrip with one Month value missing
            Ruben Marin

            Hi Manish, having a calendar can help generating the missing months:

            Data:

            LOAD Date#(Month, 'DD-MM-YYYY') as Month, Values Inline [

            Month, Values

            01-04-2015, 80615

            01-05-2015, 74193

            01-06-2015, 74193

            01-07-2015, 74193

            01-08-2015, 74193

            01-09-2015, 74193

            01-10-2015, 78336

            01-11-2015, 74193

            01-12-2015, 74193

            01-01-2016, 74893

            01-03-2016, 8357

            ];

             

            Calendar:

            Load Date(AddMonths(MinMonth, IterNo())) as Month While MaxMonth >= AddMonths(MinMonth, IterNo());

            LOAD Min(Month) as MinMonth,

              Max(Month) as MaxMonth

            Resident Data;

             

            Maybe you need to uncheck the 'Supress zero values' option in presentation tab.

             

            To know more about missing data: Generating Missing Data In QlikView

            And about calendars: How to use - Master-Calendar and Date-Values

            • Re: Cummulating in Scrip with one Month value missing
              Andrey Khoronenko

              Hi

               

              It uses the principle of creating a Master-Calendar.

               

              Month:          //Generate the first day of the month date

              LOAD

              AddMonths(Date('01.04.2015'), RecNo() - 1) as Month

              autogenerate 12;

               

               

              Left Join        //Left connection load the raw data

              LOAD*Inline

              [

              Month, Values

              01.04.2015, 80615

              01.05.2015, 74193

              01.06.2015, 74193

              01.07.2015, 74193

              01.08.2015, 74193

              01.09.2015, 74193

              01.10.2015, 78336

              01.11.2015, 74193

              01.12.2015, 74193

              01.01.2016, 74893

              01.03.2016, 8357

              ];

               

               

              Table1:          //Form a table with a cumulative sum

              NoConcatenate

              LOAD*,

              Rangesum(Values, peek('CummuativeValues')) as CummuativeValues

              Resident Month;

               

               

              DROP Table Month;

               

              The result is a table

               

              2.jpg

               

              If the raw data is loaded from an external source (for example, file xls), the end result can be formed at once by making a left connection with the accumulation the sum.

               

              Regards

              Andrey