Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi! I am having a hard time with the color expression. 'CONTRACT C' and 'CONTRACT D' are technically under the same contract so I want their numerators and denominators summed up to get a color expression. Can someone help please?
IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .95 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT A', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT B', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT C', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT D', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND MEASURE = 'CS_SERVICE_LEVEL' AND CONTRACT ='CONTRACT E', Green(270)
, Red(255))))))
Hi,
I think in the form of Virtual Table should work
Aggr(
IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .95 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT A', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT B', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT C', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT D', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT E', Green(270)
, Red(255))))))
,MEASURE,CONTRACT)
Regards
Hi,
I think in the form of Virtual Table should work
Aggr(
IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .95 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT A', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT B', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT C', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT D', Green(270)
, IF(Sum(NUMERATOR)/Sum(DENOMINATOR) >= .90 AND Only(MEASURE) = 'CS_SERVICE_LEVEL' AND ONLY(CONTRACT) ='CONTRACT E', Green(270)
, Red(255))))))
,MEASURE,CONTRACT)
Regards
That worked! Thank you so much!
So the above solution worked when both contracts being summed up have same color results i.e when they both didn't meet targets or both met targets. This month has varying result and that presented a problem. I tried exploring other formula like the one below but no luck, would appreciate other suggestions.
IF((sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>} NUMERATOR)/(sum({MEASURE = {'CS_SERVICE_LEVEL'},CONTRACT ={'CAREFIRST_A','CAREFIRST_B'}>}DENOMINATOR))>= .90, Green(270)
, Red(255)