Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I built a pivot table (image included) that includes several calculations that naturally have zero and/or null values at specific levels. However, even with suppress zero and suppress null selected, they still show up.. is there something else that I am missing?
Thanks,
Joe
Try adding MEASURE to the AGGR dimensions
Problem is that every value is a different expression, you won't be able to hide some expressions for a particular value in the dimension.
What can you do? You could try turning your different expressions into an isolated dimension.
LOAD * INLINE [
DIMAUX
Monthly Volume
Days in Month
Avg Daily Census
Paid FTEs
];
Then you have DIMAUX as a dimension and a single expression
if(DIMAUX = 'Monthly Volume', sum(something...), 0)
+
if(DIMAUX = 'Days in Month', sum(something else...), 0)
+
if(DIMAUX = 'Avg Daily Census', sum(another thing...), 0)
... etc.
Hi Daniel,
Thanks for the reply. One question. o I simply add the "if(DIMAUX = 'Monthly Volume', Monthly volume calc) to my Monthly Volume calc? If not, where do I put this code at?
Thanks,
Joe
You should remove all the expressions you currently have and add that code to your new single expression.
Got it. Let me give it a try. Thanks for the help!
When I try the following code:
if
(MEASURE='Monthly Volume', $(varMonthlyVolume) ,0)
+
if(MEASURE='Days in Month',$(varDaysInMonth) ,0)
I only get one line and that is for Monthly volume. I substituted a '1' for the the $(varDaysinMonth) and it will return the 1 and the Days in Month Row. I removed the variable reference and put in the actual calculation and that didnt work either.
What am I missing?
Post a sample qvw or post your expressions variables.
varMonthlyVolume =
Sum( aggr(distinct sum({1<MEASURE_KEY={1}>} Value), SCENARIO_DESC,MONTH,DEPT_NAME) ) *(1+ VarCensusToggle )
The VarCensusToggle is a slider bar that allows the user to change the monthly volume
varDaysInMonth =
sum
( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )
Try adding MEASURE to the AGGR dimensions
That did it, Thanks Daniel