4 Replies Latest reply: Aug 22, 2018 6:04 AM by Ganesh S RSS

    Range Selection in Qlik Sense Pivot table

    Ganesh S

      Hi Qlikerz,

       

      I have the following requirement.

       

      I have a Pivot table like below:

      Dimension                                     Measure 1                   Measure 2               Measure 3            Measure 4

      Sector# Headcount ( PY )# Headcount ( CY )          # Change        % Change
      Sector 16,6377,2966599.9%
      Sector 22,7863,10531911.5%
      Sector 31,6662,04537922.7%
      Sector 4951893-58-6.1%
      Sector 5751771202.7%
      Sector 61217541.7%
      Sector 74590-459-100.0%

       

      # Change is nothing but the diff in CY(Current Year) and PY(Previous Year) headcount and % Change is the percent in # Change.

       

      And I have a Range selection of (Change # - Measure 3) like below (only one selected at a time):

      > 100

      > 75,

      > 50,

      > 25,

      > 10,

      > 0,

      < 0,

      < -10,

      < -25,

      < -50,

      < -75,

      < -100

       

      So based on the selection of Range(Change # - Measure 3) , the Pivot table should change.


      For example, If I select ">0", the following data should be populated in the pivot table(i.e. # Change > 0).

        

      Sector# Headcount ( 2017 )# Headcount ( 2018 )          # Change        % Change
      Sector 16,6377,2966599.9%
      Sector 22,7863,10531911.5%
      Sector 31,6662,04537922.7%
      Sector 5751771202.7%
      Sector 61217541.7%

       

       

      I am able to achieve this, if I have only one measure i.e. # Change - Measure 3 in the pivot table, like below,

        

      Sector           # Change
      Sector 1659
      Sector 2319
      Sector 3379
      Sector 520
      Sector 65



      but when I include other measures and apply the same logical condition used in Measure 3 to other Measures, it does not work on other measures.

       

      I get the following output when used with multiple measures.

        

      Sector# Headcount ( 2017 )# Headcount ( 2018 )          # Change          % Change
      Sector 16,6377,2966599.9%
      Sector 22,7863,10531911.5%
      Sector 31,6662,04537922.7%
      Sector 4951893                          --6.1%
      Sector 5751771202.7%
      Sector 61217541.7%
      Sector 74590                          --100.0%

       

      Which is not expected. The highlighted rows should not be populated.

       

       

      Please let me if there is any work around for this.

       

      Hope I was able to elaborate my requirement prominently, Thanks in Advance for you help.