topic Re: need this as a % in QlikView

need this as a %

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 07:49:15 GMT

Try this:

Num(Count({<Ethnicity = {"=NullCount(Ethnicity) > 0"}>} [Client No])/Count(Ethnicity), '##.##%')

Re: need this as a %

Tue, 12 Apr 2016 07:59:59 GMT

Hi Sunny, Unfortunately that comes back with 0.00%

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 08:02:50 GMT

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)), '##.##%')

Re: need this as a %

MK_QSL — Tue, 12 Apr 2016 08:07:56 GMT

Try this.

Num(1-COUNT(If(IsNull(Ethnicity) or Len(Trim(Ethnicity))=0 or Upper(Ethnicity) = 'NULL', [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')

Re: need this as a %

Kushal_Chawda — Tue, 12 Apr 2016 08:23:10 GMT

try this

num(Count(if(lower(trim(Ethnicity))='null' or len(trim(Ethnicity))=0,[Client No])) / Count(Ethnicity),'#0.00%')

Re: need this as a %

Tue, 12 Apr 2016 09:41:53 GMT

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%

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 09:43:55 GMT

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)), '##.##%')

Re: need this as a %

Tue, 12 Apr 2016 09:51:51 GMT

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

Re: need this as a %

Kushal_Chawda — Tue, 12 Apr 2016 09:55:28 GMT

try this

Num(1-COUNT(If(match(lower(Ethnicity), 'null','refused'), [Client No]))/COUNT(TOTAL Ethnicity),'#,##0.0%')

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 09:55:30 GMT

May be this:

Num(1 - (Count({<Ethnicity = {'NULL', 'Refused'}>} Ethnicity)/Count (Ethnicity)), '##.##%')

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 09:57:12 GMT

Kush don't you think that this can be a set analysis expression rather than if statement?

Re: need this as a %

Kushal_Chawda — Tue, 12 Apr 2016 10:08:34 GMT

Yeah. it is better with set analysis, but this should also work

Re: need this as a %

sunny_talwar — Tue, 12 Apr 2016 10:10:32 GMT

Agreed

Re: need this as a %

MK_QSL — Tue, 12 Apr 2016 10:16:36 GMT

chris.fray It's more than 3 hours, you are getting replies on this post which I think not much difficult.

It's always a best option to provide a sample app to get a quick answer.