0 Replies Latest reply: Jan 9, 2018 10:36 PM by Mei Zheng Tay RSS

    Show all values (incl. null) in pivot table

    Mei Zheng Tay

      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