Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Partner
Partner

Autogenerate Rows for A Pivot Table in Qlik Sense

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.

Tags (2)