Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
wynnjimbbt
Contributor III
Contributor III

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

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 ={"*"}

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

2 Replies
rubenmarin

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
Contributor III
Contributor III
Author

thank you!