topic Re: if(aggr(eFinPlanFee),field...) trouble in a calculated Dimension in QlikView

if(aggr(eFinPlanFee),field...) trouble in a calculated Dimension

wynnjimbbt — Wed, 25 Nov 2020 16:16:04 GMT

I need to filter the zero data rows from a chart. I have created a calculated dimension; "fltr1" defined below.

=aggr($(eFinancialPlanningFees),Year,Group, [Region Team],Advisor,[Profile Name])

returns correct $ amounts and zero on the data rows I need to exclude

Issue:

=if(aggr($(eFinancialPlanningFees),Year,Group, [Region Team],Advisor,[Profile Name])=0,null(),1)

when I add the if statement fltr1 returns all 1s; so suppress nulls is not effective.

note: $(eFinancialPlanningFees) works correctly returning $ values

eFinancialPlanningFees =

sum( $(eProductionSetModifier) [Fee Based Revenue] * FinancialPlanningFlag )

eProductionSetModifier) =

{ $ < $(sProduction)> }

sProduction =

Disp = {'QW', 'W' }, [Sales Cycle #] = {4}, [Event Type] = {'Disp Date'} , NonPTSFlag = {1} , LogonID ={"*"}

rubenmarin — Mon, 09 Jul 2018 17:53:31 GMT

Hi Jim, in presentation tab there is an option to supress zero values, checking this box can hide those rows.

Besides that, the 'If' should be inside the aggr to evaluate row by row:

=aggr(If($(eFinancialPlanningFees)<>0, $(eFinancialPlanningFees)),Year,Group, [Region Team],Advisor,[Profile Name])

wynnjimbbt — Mon, 09 Jul 2018 18:20:38 GMT

thank you!