Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This is a really simple question I'm sure but I just can't get my head around it!
I'm trying to create a percentage column from a straight table so I have -
Region Count
Blank 2084
North America 765
Europe 411
Asia 339
Middle East 236
South America 225
Australasia 306
Caribbean 182
Africa 137
Total 4339
The sum for this count is -
count(DISTINCT(if(ukmpsmr_risk_tracker.SLIP_STATUS='BOUND' or ukmpsmr_risk_tracker.SLIP_STATUS=
'BOUND - SPECIAL ACCEPTANCE' or ukmpsmr_risk_tracker.SLIP_STATUS='BOUND - SPECIAL ACCEPTANC',
ukmpsmr_risk.UMR)))
What I'm after is a percentage column. I've tried -
Count(if(ukmpsmr_risk_tracker.SLIP_STATUS='BOUND' or ukmpsmr_risk_tracker.SLIP_STATUS=
'BOUND - SPECIAL ACCEPTANCE' or ukmpsmr_risk_tracker.SLIP_STATUS='BOUND - SPECIAL ACCEPTANC',
ukmpsmr_risk.UMR))/Count(total(if(ukmpsmr_risk_tracker.SLIP_STATUS='BOUND'or ukmpsmr_risk_tracker.SLIP_STATUS=
'BOUND - SPECIAL ACCEPTANCE' or ukmpsmr_risk_tracker.SLIP_STATUS='BOUND - SPECIAL ACCEPTANC',
ukmpsmr_risk.UMR)))
This however isn't bringing in the correct figures, could it be to do with the Distinct part in the count?
Kev
It's certainly possible that the distinct count will return a different number than a plain count.
What you can try is enabling the option Relative for the expression (or a copy of that expression). You can find the option on the Expressions tab of the chart properties window.
BTW, you can rewrite your expression to something that should perform better and looks less complicated:
count({<ukmpsmr_risk_tracker.SLIP_STATUS={'BOUND','BOUND - SPECIAL ACCEPTANCE','BOUND - SPECIAL ACCEPTANC'}>}DISTINCTukmpsmr_risk.UMR)