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

[wynnjimbbt]

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 · Accepted solution]

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]

thank you!