7 Replies Latest reply: Feb 27, 2017 1:16 PM by Marco B RSS

    Qlik Sense Pivot Table Custom Row

    Marco B

      Is it possible to include on the row of a Pivot Table the dimension plus the aggregate of one of the measures in a  fact table.

      Example:

       

      InvoiceId, YearMonth, MonthSeq,Billed, Paid

      1,201701,200,0,100

      2,201701,100,0,50

      3,201701,50,1,20

      4,201702,300,0,100


      Current Pivot Table   (YearMonth  , Paid)

      YearMonth                       0                 1 ...

      201701                          150              20

      201702                          100


      Need the following: (YearMonth Billed  , Paid)

      YearMonth / Billed            0                 1 ...

      201701  -  350                150              20

      201702  -  300                100


      Or better, would it be possible to add a single column just for the sum of billed for YearMonth ? I don't know if there is a way to include on rows since it is a measure that I am adding.


        • Re: Qlik Sense Pivot Table Custom Row
          Aehman K

          Yes you can merge depending on what your expression is?

          You can create a new dimension our of your measure but Aggr expression will not work in Script.

           

          If you're anyhow able to modify your expression to create Billed as dimension then you can use

           

          YearMonth&' - ' & Billed as [YearMonth Billed]

           

          Which should give you the view you looking for.

          BTW, what's your expression for Billed?

            • Re: Qlik Sense Pivot Table Custom Row
              Marco B

              Billed=Sum(Billed), but better than my initial proposed concatenation (and probably a cleaner way) would be to keep the YearMonth dimension by itself on rows and add a new measure column (BilledAmount). However, the only way I know how to do iis to add to "columns", but that would replicate the billed values on all the columns and I only need it to show once, as a first column. Is there a way the Billed values could be displayed on a column by itself ?

            • Re: Qlik Sense Pivot Table Custom Row
              omar bensalem

              Marco,

               

              This is what I've done:

               

              here's the table I'm working with :

               

              load * Inline [

              InvoiceId, YearMonth, MonthSeq,Billed, Paid

              1,201701,200,0,100

              2,201701,100,0,50

              3,201701,50,1,20

              4,201702,300,0,100

              ];

               

               

              1) I've created a variable called vSumBilled= sum({1}Billed) to always get the total sum (billed) independant of the selections

               

              Capture.PNG

               

              2) then, in the pivot table I've created a dimension like this:

               

              =YearMonth&' / '&Billed (activate the total and name it Sum billed

               

               

              3) The measure is like this:

              =IF(Dimensionality() = 0, $(vSumBilled), Sum(Paid))

               

              The result is as follows:

               

              Capture.PNG

               

               

              Hope this helps,

              Omar,