Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
cbaqirdhds
Valued Contributor

Pivot Table Expression Cascades when grouped

I am trying to figure out what is wrong with my syntax that causes the Variance and Forecast Accuracy to cascade to all rows when grouped by Program/Portfolio but if a single Project is selected, it looks correct.

12-9-2015 8-26-05 AM.jpg

Single and Correct:

12-9-2015 8-27-42 AM.jpg

Dimensions:

PROG_PORT

REQUEST_ID

Variables and Their Expressions:

vNewForecastAccuracy  =IF(($(vForecastRound)=0 and $(vActualRound)=0),IsNull(Null()),IF(FABS($(vNewVariance))>1,0,1-FABS(($(vNewVariance)))))

vNewVariance  =IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound)))))

Thanks,

Cassandra

12 Replies
Not applicable

Re: Pivot Table Expression Cascades when grouped

hi,

you must remove the sign = in your variables

Variables and Their Expressions:


vNewForecastAccuracy 

IF(($(vForecastRound)=0 and$(vActualRound)=0),IsNull(Null()),IF(FABS($(vNewVariance))>1,0,1-FABS(($(vNewVariance)))))

vNewVariance 

IF($(vVariance)=0,0,IF($(vForecastRound)=0,IF($(vVariance)<0,-1,1), ($(vVariance)/ ($(vForecastRound)))))

mwoolf
Honored Contributor II

Re: Pivot Table Expression Cascades when grouped

Here's a guess:

Your variables contain calculations. Those calculations are not done in the chart and therefore are not aggregated by the chart dimensions.

cbaqirdhds
Valued Contributor

Re: Pivot Table Expression Cascades when grouped

Why aren't the calculations done in the chart? It works fine when I select one project.

mwoolf
Honored Contributor II

Re: Pivot Table Expression Cascades when grouped

I presume that you have calculations in your variables. Those calculations are not limited by the chart dimensions.

When you select a project, you are limiting the calculation in your variables.

cbaqirdhds
Valued Contributor

Re: Pivot Table Expression Cascades when grouped

So is the answer to not use a variable with calculations in a chart or to add the chart dimensions into the statement? If the latter, how would I do that?

cbaqirdhds
Valued Contributor

Re: Pivot Table Expression Cascades when grouped

Why would that make a difference?

cbaqirdhds
Valued Contributor

Re: Pivot Table Expression Cascades when grouped

It seemed to fix vNewVariance  but not vNewForecastAccuracy.

Not applicable

Re: Pivot Table Expression Cascades when grouped

Can you show me the result for vNewForecastAccuracy

cbaqirdhds
Valued Contributor

Re: Pivot Table Expression Cascades when grouped

12-10-2015 9-13-32 AM.jpg