Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Re: Suppress zeros and nulls in pivot table

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?

Highlighted
danielrozental
Honored Contributor II

Re: Suppress zeros and nulls in pivot table

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?

Highlighted
Not applicable

Re: Suppress zeros and nulls in pivot table

Its seems to pass the expression checker fine, it just yields a null value
this calculation is made up of several pieces.  These pieces are created in variables.  I do have it simplified as such:
avg
(
aggr(
DISTINCT
(
(
$(varAvgDailyCensus) * $(varPaidHourPerStat) *$(varDaysInMonth)) /
(2080/365*
$(varDaysInMonth))
),
MEASURE, FORECAST_LEVEL_DESC, MONTH,DEPT_NAME
)
)
If I embed this calculation (wth the variables) it returns the correct value.  If I embed this into a variable (varPaidFTEs), It yields a null value.  However, if I create another expression and reference it, it brings back the same value for all columns.  When I saw this, I thought it was due to the variables it was referencing (varAvgDailyCensus, varPaidHourPerstat, etc), so I embedded those calcs into varPaidFTEs.
Still trying to grasp the logic engine in Qlikview!