topic Let the user change the analysis unit ?? in QlikView

Let the user change the analysis unit ??

Thu, 16 May 2013 13:44:03 GMT

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

Re: Let the user change the analysis unit ??

Thu, 16 May 2013 14:28:46 GMT

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

)

Re: Let the user change the analysis unit ??

jpapador — Thu, 16 May 2013 14:37:48 GMT

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