topic Re: Calculate a percentage in QlikView

Calculate a percentage

dezmundw15 — Thu, 12 Oct 2017 17:06:50 GMT

I am new to Qlik Sense and I am trying to calculate an allocation percentage. I am using Qlik Sense and I am utilizing a table. I have the following column and rows

Provider	Facility	Patients Seen by Clinic
Totals		200
Jenny	Clinic 1	50
Mary	Clinic 3	100
Suzie	Clinic 4	50

I would like to calculate the Patients Seen by Clinic/Provider or 50/200. I am confused with the Aggr functions and the overall structure of the syntax

Sum(Provider)/ Sum(Patients Seen by Clinic should give me a % across the distribution of patients seen in the clinics...but I am getting nothing. Can someone help me.

Thanks,

Desmond

Re: Calculate a percentage

sunny_talwar — Thu, 12 Oct 2017 19:42:30 GMT

May be this

Sum(Provider)/Sum(TOTAL Provider)

Re: Calculate a percentage

dezmundw15 — Thu, 12 Oct 2017 20:04:04 GMT

Sunny,

Thanks for getting back to my ask...I tried that very same function and it come back with nothing ( - ). I think it has something to do with the Qlik table calculating the "Patients Seen by Clinic"column is summarized based on the Provider column. In the Data Section under Column (Provider) the function is calculating as =[Provider]. The summarized results is based on this column.

I have received results calculating the %, but it would read as follows: 200000%, 50000%, 100000% and so on.

Re: Calculate a percentage

dezmundw15 — Thu, 12 Oct 2017 20:06:25 GMT

Sunny,

I have used the following as well, and I just get 0% for everything

sum([PROVIDER])/textcount([PROVIDER])

Re: Calculate a percentage

sunny_talwar — Thu, 12 Oct 2017 23:45:11 GMT

Would you be able to share a sample or an image to show what you have?

Re: Calculate a percentage

rwunderlich — Fri, 13 Oct 2017 03:12:06 GMT

Sunny,

I think you meant:

Dimensions:

Provider

Facility

Measure:

Sum([Patients seen by Clinic] / Sum(TOTAL [Patients seen by Clinic]

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 11:38:51 GMT

Yes you are right

Re: Calculate a percentage

pradosh_thakur — Fri, 13 Oct 2017 13:34:06 GMT

may be something like this

Sum([Patients seen by Clinic] / Sum(TOTAL [Patients seen by Clinic])

please check your field name as this may contain extra space .

Sum([Patients seen by Clinic] / Sum({1} [Patients seen by Clinic])

regards

Pradosh

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:27:09 GMT

Sunny here is a screen shot

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 16:37:07 GMT

Did you try with Rob's expression

Sum([Patients seen by Clinic] / Sum(TOTAL [Patients seen by Clinic])

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:40:14 GMT

I tried everyone's suggestions to no avail. I am wondering if it has something to do with being text. I am reviewing functions now, to see if I need to somehow convert it. This is basically an uploaded Excel file put into Qlik.

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 16:42:08 GMT

What is your expression for Patients seen by Facility? Is it using Count? Then try this

Count([Patients seen by Clinic] / Count(TOTAL [Patients seen by Clinic])

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:45:59 GMT

Sunny,

Anytime I use the TOTAL fx, it gives me an Error in expression.

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 16:48:06 GMT

What is your expression my friend?

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:48:24 GMT

Sunny,

Yes, I am using a Count to get the distribution of patients seen at each clinic. So for the Patient Seen %, I used the same field, but wanted to get a % instead of a count, to denote as distribution/allocation.

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 16:48:58 GMT

Expression please

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:55:25 GMT

This is the actual expression that is being used in the Patients seen by Facility

Count([SEEN BY PROVIDER]) and I get the count which is what is depicted in the screen shot

If I use the following:

Sum([SEEN BY PROVIDER]) - I just get 0.00%

If I use the following:

Sum([SEEN BY PROVIDER])/Sum(Total([SEEN BY PROVIDER]) - I get Error in expression

Re: Calculate a percentage

sunny_talwar — Fri, 13 Oct 2017 16:56:10 GMT

Try this

Count([SEEN BY PROVIDER])/Count(TOTAL [SEEN BY PROVIDER])

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:57:15 GMT

The only way I am getting Total to display in the Table is using the Totals button in the Presentation Tab. If I turn off, then the total goes away

Re: Calculate a percentage

dezmundw15 — Fri, 13 Oct 2017 16:59:21 GMT

OMG....It Worked!!!!

Sunny Thank You so so much!!!