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.
