0 Replies Latest reply: Dec 6, 2016 12:27 PM by George Barrett RSS

    Autogenerate Rows for A Pivot Table in Qlik Sense

    George Barrett

      I am trying to make a pivot table in Qlik Sense with the dimension Employee, the column Month, and the measure as the Sum of Minutes with set analysis that equates to Exception = 'Coaching.' The Month field comes from a Master Calendar and the Master Calendar is associated to the main table by the Date field (only these 2 tables). Before a Month selection is made, if an Employee did not have any Minutes in the month it is represented by a dash. However, once I filter by Month, the rows of Employees with no Minutes disappear. I want to be able to display these rows with 0. Both the Employee and Month fields have "DisplayNull Values" checked. I tried wrapping the measure in an IF(ISNULL()) statement but that didn't work. So I think I need to autogenerate dummy rows. See the example below:

          

       

      Employee

      Date

      Minutes

      Exception

      John Doe

      10/30/2016

      30

      Coaching

      John Doe

      12/30/2016

      45

      Coaching

       

      I would like to generate at least 1 dummy row for each Employee for each Month with the Minutes set to 0 and the Exception set to Coaching.

          

      Employee

      Date

      Minutes

      Exception

      John Doe

      10/30/2016

      30

      Coaching

      John Doe

      11/30/2016

      0

      Coaching

      John Doe

      12/30/2016

      45

      Coaching

       

      The reasoning behind this is that the dummy rows would fall into the set analysis (Exception = Coaching) and would display in the pivot table. There are other fields in the table but besides the ones above the rest of the fields can be null. I have tried multiple methods but none of them have been successful. Can anyone offer any insight into this? Thank you.