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.
