Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a pivot table as shown below
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