Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Anybody help us get the following expression to show as a % on a straight table?
100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity))
the usual, '#.##' or changing number format doesn't work.
Chris
May be this:
Num(1 - (Count({<Ethnicity = {'NULL', 'Refused'}>} Ethnicity)/Count (Ethnicity)), '##.##%')
Try this:
Num(Count({<Ethnicity = {"=NullCount(Ethnicity) > 0"}>} [Client No])/Count(Ethnicity), '##.##%')
Hi Sunny, Unfortunately that comes back with 0.00%
I think the issue is the numerator part. Can you try to see if NullCount(Ethnicity) give you the right number for the numerator part? If it does, try this:
Num(NullCount(Ethnicity)/Count(Ethnicity), '##.##%')
Else, sticking with your expression, may be this:
Num(100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%')
Try this.
Num(1-COUNT(If(IsNull(Ethnicity) or Len(Trim(Ethnicity))=0 or Upper(Ethnicity) = 'NULL', [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')
try this
num(Count(if(lower(trim(Ethnicity))='null' or len(trim(Ethnicity))=0,[Client No])) / Count(Ethnicity),'#0.00%')
Tried NullCount(Ethnicity) but it gives a result of 0 - this is wrong as you can see:
Num(100 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%') works but creates the figure in the wrong format : 9996.77%
Ass pointed by Manish, you should be subtracting from 1 and not 100 here. His suggestion should work or try this:
Num(1 - (Count (if (Ethnicity = 'NULL',[Client No]))/Count (Ethnicity)), '##.##%')
Yes this looks better. Its in a % percentage but need to tweak.
The calculation we need is 100% - Null - Refused (another category - see table below). The expression at the minute is only doing NULL
try this
Num(1-COUNT(If(match(lower(Ethnicity), 'null','refused'), [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')