Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
meizheng
New Contributor III

Show all values (incl. null) in pivot table

Hi all,

I have a pivot table as shown below

pivot_table1.png

I have a total of 5 possible values for the 'Overdue' field - < 3 Months, >= 3 Months, >= 6 Months, >= 1 Year and >= 2 Years

I would like to display all 5 values for each Dept.

Have tried using the following expressions for the measure to force show all values but to no avail

-if(Sum({1} [2nd dimension]) > 0, Sum([Sales]),0)

//force show all values even if Dept has 0 values

- if (sum({Ignore All dimensions} Sales) <> sum(Sales), sum({Your set analysis} sales, 0)

//from Show all values in Pivot/Straight Table (irrespective of current selection)

My current expression for the measure is:

if(Count({1} [Overdue]) > 0,

     if(Count({$<[VA.Month]={'$(=Max([VA.Month]))'}, varMonth={"0"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 2nd mth?"={'Yes'}, varMonth={"1"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 3rd mth?"={'Yes'}, varMonth={"2"}, [VA.isOverdue]={'Yes'}>}

            distinct [%ItemID])='0', Null(), //if equals 0 then Null else count items

Count({$<[VA.Month]={'$(=Max([VA.Month]))'}, varMonth={"0"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 2nd mth?"={'Yes'}, varMonth={"1"}, [VA.isOverdue]={'Yes'}>+

     <[VA.Month]={'$(=Max([VA.Month]))'}, "Found in 3rd mth?"={'Yes'}, varMonth={"2"}, [VA.isOverdue]={'Yes'}>}

            distinct [%ItemID])), 0)


For context, attached is majority of the load script for this table. (Deleted some fields which are not used in this pivot table)

Any idea what could be the cause/how to display all values? TIA!

-Serena