Autogenerate Rows for A Pivot Table in Qlik Sense

gbarrett — Tue, 06 Dec 2016 17:27:20 GMT

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.

topic Autogenerate Rows for A Pivot Table in Qlik Sense in App Development

Autogenerate Rows for A Pivot Table in Qlik Sense