Aggregation second paramerter dependent on user selection

[phuelish]

Hello,

I'm trying to build an expression for a gauge chart that sets the maximum based on user selections.

The user can review the percentage of incomplete sales by [District Name],[VP Name],[State Name],[Sales Manager] and [Sales Representative].  The dashboard will have a straight table showing the ranking of whatever the user selects based on the next higher level of the hierarchy.  If the user selects a sales representative, it'll show a list of all sales reps with the same sales manager, and where the sales rep ranks within the sales manager's group. I want to dynamically set the gauge max, since there's a wide degree of variance in percentage of incomplete sales between sales managers.

As an example, The gauge chart will show the result for the selection (i.e. sales rep), with a gauge max based on the highest sales rep.  The expression below works great for setting the max if I explicitly name the aggregation second parameter, but doesn't work if I use an if statement to determine the second parameter (Bad Min or Max Value in Gauge):

Working Gauge MAX expression

max(AGGR(CEIL(IF(SUM( {$<[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-11))"}>}[Sales])>24,Sum( {$<[Sale_Completion]={0},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-11))"}>}[Sales])/SUM( {$<[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-11))"}>}[Sales]),0),0.01),[State Name]))

I've tried replacing [State Name] with a conditional statement, but haven't had any luck:

if(getselectedcount([Sales Rep])>0,[Sales Rep],[State Name])

Does the AGGR function allow a dynamic second parameter?  If so, how do I fix my expression?

Thanks!

[phuelish]

I "think" I figured out how to make this work.

I'm using a button to set desired aggregation field using the system field '$Field' and a multi-tiered if statement:

=if((getselectedcount([State Name])+getselectedcount([Sales Manager]))>0,'Sales Rep',IF(getselectedcount([VP Name])>0,'Sales Manager',if(getselectedcount([District Name])>0,'VP Name',District Name')))

I'm then using [$Field] as my aggregator.

Unfortunately, since I don't have developer rights to our QV, I can't use triggers or macros to set variables.

I've got two concerns...

- First, the field I'm using is a system field.  I don't know how it is used elsewhere within the application, so am concerned about using this as the variable.  That said, it works because it already contains all of the field names within the application.

- Second, it requires the user to click on a button.  Ideally, I'd refresh the aggregator field whenever the dimension field selections changes.  If there's a way to do this without accessing triggers or macros, ideally by variable rather than with the system field, I'd appreciate any suggestions.

Thanks!

-Frank

[phuelish]

Hi,

Hopefully someone is learning something as a result of my trials-and-errors. The $Field idea didn't work as I expected, so I used the Stefan WALTHER's Nested If Statement creator to develop the following conditional:

if(getselectedcount(Sales_Rep)>0,MAX(AGGR(CEIL(IF(SUM( {$<Sales_Rep=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales)>24,Sum( {$<[Product_No]={500,504,770},Sales_Rep=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales )/SUM( {$<Sales_Rep=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales),0),0.01),Sales_Rep)),

    if(getselectedcount(Sales_Mgr)>0,MAX(AGGR(CEIL(IF(SUM( {$<Sales_Mgr=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales)>24,Sum( {$<Sales_Mgr=,[Product_No]={500,504,770},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales )/SUM( {$<Sales_Mgr=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales),0),0.01),Sales_Mgr)),

        if(getselectedcount([VP Name])>0,MAX(AGGR(CEIL(Sum( {$<[VP Name]=,[Product_No]={500,504,770},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales )/SUM( {$<[VP Name]=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales),0.01),[VP Name])),

            if(getselectedcount([State Name])>0,MAX(AGGR(CEIL(Sum( {$<[Product_No]={500,504,770},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"},[State Name]=>}Sales )/SUM( {$<[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"},[State Name]=>}Sales),0.01),[State Name])),MAX(AGGR(CEIL(Sum( {$<[District]=,[Product_No]={500,504,770},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales )/SUM( {$<[District]=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales),0.01),[District]))))))

I want the max to be set to the highest result for the level of detail selected based on the hierarchical parent. As an example, if I select a sales rep, I want the gauge max to be the highest sales rep working for that sales manager.  I thought that removing the criteria within the set analysis would allow me to do this, but not so...I suspect that the aggregation term is causing the calculation to ignore the criteria change.

I'd go with a static max, but the range of results expand quite a bit as it gets more granular, even when limiting the analysis to only sales reps with at least 25 contracts. Any ideas on how to do this would be greatly appreciated, especially if I'm headed for a dead end.

Thanks!

-Frank

[phuelish · Accepted solution]

Finally! Got it figured out...I removed the selections within Sum aggregation, but I also needed to remove the selections within the Max aggregation...it has to be removed in both places.  For simplicity:

MAX({<[District]=>} AGGR(CEIL(Sum( {$<[District]=,[Product_No]={500,504,770},[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales )/SUM( {$<[District]=,[Month Name]= {">=$(=addmonths(monthname(ReloadTime()),-13))"}>}Sales),0.01),[District]))