Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I understand that checking the suppress zero-values box on the Presentation tab will not do the trick ... how can I suppress rows when the Total column is not greater than zero? I have a pivot table with two dimensions (Supervisor and Name) and a horizontal dimension (Carrier). The below formula will suppress the zero values at the Supervisor level, but not the Name level. Any ideas?
This is my current expression formula:
=If(sum(IncludeComp) > 0,
If(SecondaryDimensionality()=0,
Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp) & ' (' &
Num(Sum({<Carrier = {'Apples','Oranges'}, CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>}IncludeComp) /Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp),'#.00%','.',',')
& ')', Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp)))
Here is an example of what the above formula results in (I want the first row to not show):
Can you try this:
=If(sum(IncludeComp) > 0, If(SecondaryDimensionality()=0,
Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp) &
Num(Sum({<Carrier = {'Apples','Oranges'}, CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>}IncludeComp) /Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp),'(#.00)%','.',','), Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp)))
See if the below link will be any help...
Would you be able to provide sample
OR PFA.
It Should be this format
You can in dimension Name, change for:
if(sum(.....) >0, Name)
and check Supress values null in this dimension
Can you try this:
=If(sum(IncludeComp) > 0, If(SecondaryDimensionality()=0,
Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp) &
Num(Sum({<Carrier = {'Apples','Oranges'}, CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>}IncludeComp) /Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp),'(#.00)%','.',','), Sum({<CreateDate = {"$(=Date(Today()-1, 'M/D/YYYY hh:mm:ss tt'))"}>} IncludeComp)))
Thanks Sunny ... you're knowledge/expertise is impressive.
No problem at all, I am glad I was able to help