1 Reply Latest reply: Aug 1, 2018 7:46 AM by Sunny Talwar RSS

    YTD Calculation in pivot table in qliksense

    Rachana Jannapureddy

      YTD should be calculated as per below in the attached qvf:

       

       

      Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9Header 10Header 11Header 12Header 13Header 14Header 15Header 16

         

      Jan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16Nov-16Dec-16Jan-17Feb-17
      3,6647,27210,56414,51217,67821,47026,86230,84034,26139,70544,31548,0653,0939,252
      13,82927,98433,36838,47941,13243,36345,85648,15051,79054,98158,34861,1676,88215,157
      37761482172814015106057569331,2331,471274516
      9917525331338449255461369178386291259101
      5713521127637345752760066576588096576139
      2575438171,0641,3011,5461,7712,0023,7495,7457,6608,876262493
      4812151722242931323236
        • Re: YTD Calculation in pivot table in qliksense
          Sunny Talwar

          May be try this

           

          If(SecondaryDimensionality() = 2, Sum(Aggr(RangeSum(Above(Sum(VALUEs), 0, RowNo())), Region, Country, Year, (Month, (NUMERIC)))), Sum(VALUEs))

           

          Where Month and Year are created in the script like this

          LOAD

              ID,

              Country,

              Date(CretaeDATE) as CretaeDATE,

             Year(CretaeDATE) as Year,

              Month(CretaeDATE) as Month,

              Region,

              VALUEs

          FROM [lib://desktop/Data (1).xlsx]

          (ooxml, embedded labels, table is Sheet1);


          Capture.PNG