Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like for the user to be able to change the analysis unit in my map. Right now I have it hard coded to color the bubbles based on "Win Rate" (number sold / total sales opportunities), as follows:
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCT OpportunityID),'####,##0.0%') < .1, RGB(250,0,0),
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCT OpportunityID),'####,##0.0%') > .15, RGB(0,250,0),
RGB(250,250,75)
)
)
In the attached picture you will see that I have added buttons to toggle between "WinRate" and "Opportunity USD". The option list is populated using the following supporting table:
Metrics:
LOAD * INLINE [
_metrics
Opportunity USD
vWinRate
];
How do I reference these metric selections in further conditional logic, something like the following pseudo code:
IF vWinRate = selected then:
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCT OpportunityID),'####,##0.0%') < .1, RGB(250,0,0),
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCT OpportunityID),'####,##0.0%') > .15, RGB(0,250,0),
RGB(250,250,75)
)
)
IF Opportunity USD = selected then:
if(num(sum([Opportunity USD])) < 1000000, RGB(250,0,0),
if(num(sum([Opportunity USD])) > 20000000, RGB(0,250,0),
RGB(250,250,75)
)
)
can't you just put this ? (almost your pseudo code)
i havent tested it, so maybe you ll have to correct it
first make sure to check 'always one selected value' in your _metrics field selection box
then the code :
IF(_metrics=vWinRate ,
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCTOpportunityID),'####,##0.0%') < .1, RGB(250,0,0),
if(num(count(distinct(if(Match(Status, '06 - Closed - Sold'),OpportunityID))) / Count(DISTINCTOpportunityID),'####,##0.0%') > .15, RGB(0,250,0),
RGB(250,250,75)
)
)
,
if(num(sum([Opportunity USD])) < 1000000, RGB(250,0,0),
if(num(sum([Opportunity USD])) > 20000000, RGB(0,250,0),
RGB(250,250,75)
)
)
)
Make one expression for each selector and then make a conditional for each expression, no if statement required.
SubStringCount(Concat(_metrics, '|'), 'vWinRate')
SubStringCount(Concat(_metrics, '|'), 'Opportuninty USD')