topic Re: Pivot Table Expression Cascades when grouped in QlikView

Pivot Table Expression Cascades when grouped

cbaqir — Wed, 09 Dec 2015 15:30:43 GMT

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.

Single and Correct:

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

Re: Pivot Table Expression Cascades when grouped

Wed, 09 Dec 2015 16:55:49 GMT

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)))))

Re: Pivot Table Expression Cascades when grouped

m_woolf — Wed, 09 Dec 2015 17:11:55 GMT

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.

Re: Pivot Table Expression Cascades when grouped

cbaqir — Wed, 09 Dec 2015 17:24:46 GMT

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

Re: Pivot Table Expression Cascades when grouped

m_woolf — Wed, 09 Dec 2015 18:49:07 GMT

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.

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 14:38:48 GMT

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?

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 14:39:26 GMT

Why would that make a difference?

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 15:09:31 GMT

It seemed to fix vNewVariance but not vNewForecastAccuracy.

Re: Pivot Table Expression Cascades when grouped

Thu, 10 Dec 2015 16:07:28 GMT

Can you show me the result for vNewForecastAccuracy

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 16:14:08 GMT

Re: Pivot Table Expression Cascades when grouped

Thu, 10 Dec 2015 16:26:04 GMT

we are going to test somthing.

put in your variable this expression : IF( FABS($(vNewVariance))>1 , 0 ,1- FABS($(vNewVariance)) )

and show me the result

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 19:35:47 GMT

Still null.

Re: Pivot Table Expression Cascades when grouped

cbaqir — Thu, 10 Dec 2015 21:37:44 GMT

Ok, after removing the = from the Variables, the vNewVariance is working but vNewForecast is still showing all NULLs.

When I copied the expression into a text box, I see Error: Error in expression: ')' expected.

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

I think the issue is related to FABS($(vNewVariance)) because this alone gives me the same error. Does FABS() not work in this case for some reason?

Is there a way to pinpoint the issue because I can't seem to find it.