Percentages

[kevbrown]

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

[Gysbert_Wassenaar]

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)