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
I do have a follow-up question. I am in the process of converting my expressions and ran into a issue that I dont understand. When I put the following code (see below) into a variable and reference it in an expression, it shows up. However, if I put it in the code block as discussed above, it errors out. I did make sure I put the Measure dimension into the Aggr.
However, if I embed the calculation directly into the If script (as described above) it works.
Here is the variable expression:
=
avg(
aggr(
DISTINCT
(
(
avg( aggr(distinct sum({1<MEASURE_KEY={1}>} Value),MEASURE, FORECAST_LEVEL_DESC, SCENARIO_DESC, MONTH,DEPT_NAME) ) /
avg( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE, FORECAST_LEVEL_DESC, SCENARIO_DESC, MONTH,DEPT_NAME) )
*
avg( aggr(distinct sum({1<MEASURE_KEY={9}>} Value),MEASURE, SCENARIO_DESC, MONTH,DEPT_NAME) ) *
sum( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE, SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )
) /
(2080/365* sum( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE,SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )
)
),MEASURE,FORECAST_LEVEL_DESC,SCENARIO_DESC, MONTH,DEPT_NAME
)
)
This calculation is referenced alot, so would rather not have to put this large chunk in every time.
Any thoughts?
Does the expression fail or the syntax checker complains about it?
Your expression seems overly complex, why would you need to do an aggr over an aggr?