topic Re: ratio calculation in QlikView

ratio calculation

Tue, 29 Dec 2015 15:24:34 GMT

Hi every one. I am new in Qlik and want to ask a question. I have a set of data with 5 variable. one of them is school name. In this variable how can I calculate ratio of one school vs all others.

Re: ratio calculation

oknotsen — Tue, 29 Dec 2015 15:31:41 GMT

I guess you mean fields instead of variables.

Can you post an example of the data you have, so we can give you a good example of a calculation in return?

Re: ratio calculation

sunny_talwar — Tue, 29 Dec 2015 16:04:55 GMT

Dimension: School Name

Expression: Sum(FieldName)/Sum(TOTAL FieldName) or Count(FieldName)/Count(TOTAL FieldName)

Re: ratio calculation

Wed, 30 Dec 2015 09:47:36 GMT

Hi Onno, Thank you for your response. I will try to send you a sample file.

Re: ratio calculation

oknotsen — Wed, 30 Dec 2015 09:51:54 GMT

Don't try to send me something . See if you can post a simply screenshot or a table of a small data dump (5 rows, related columns) in a reply.

Unless of course what Sunny posted below already solves it .

Re: ratio calculation

Wed, 30 Dec 2015 09:54:00 GMT

Hi Sunny. First of all thank you for your answer but in my case filed names are same like below sample.

name sname schoolname age sex

j g medical 25 M

a c nutrition 22 F

b d nursing 23 M

and so on. now I want to calculate ratio of nursing vs total of schoolname.

Re: ratio calculation

Wed, 30 Dec 2015 10:13:27 GMT

Re: ratio calculation

Wed, 30 Dec 2015 10:21:23 GMT

=count({<schoolname={'nursing'}>schoolname})/count(TOTAL schoolname)

Re: ratio calculation

Anonymous — Wed, 30 Dec 2015 10:27:58 GMT

try like this:

=sum({<schoolname={'nursing'}>} FieldName) //will give for nursing only

sum(<Total schoolname> FieldName) //will give for all school

Hope this will help!!

Re: ratio calculation

Anonymous — Wed, 30 Dec 2015 10:28:43 GMT

Also dont forget to change the format i.e show in percent under Number tab

Re: ratio calculation

sunny_talwar — Wed, 30 Dec 2015 11:46:37 GMT

I don't think TOTAL is required here, you can just use (and note the missing curly bracket in red)

=Count({<schoolname={'nursing'}>} schoolname)/Count(schoolname)

and if you are doing this in a straight table with schoolname as your dimension, then try this:

=Count(schoolname)/Count(TOTAL schoolname)

Re: ratio calculation

Anonymous — Wed, 30 Dec 2015 12:33:22 GMT

From my point of view, I think Total is required.

The reason behind this is: If I am having any measure field like Sales, NoOfFaculty then I need to group w.r.t Dim like

=sum(<Total SchoolName> NoOfFaculty)

Re: ratio calculation

sunny_talwar — Wed, 30 Dec 2015 13:29:28 GMT

Here is any example to show that TOTAL is not required

Re: ratio calculation

Anonymous — Wed, 30 Dec 2015 13:31:01 GMT

Did you try what I suggested you??

Replace FieldName with Score or Course...

Re: ratio calculation

Wed, 30 Dec 2015 17:59:09 GMT

Thank you all for your answers and special thank for dear Sunny

Re: ratio calculation

sunny_talwar — Wed, 30 Dec 2015 18:26:18 GMT

No problem

Re: ratio calculation

Anonymous — Thu, 31 Dec 2015 05:21:02 GMT

Sunny,

For me School is a Dim not a measure, Even you would be agree on same.

Now If I want to see score % then:

I would go for this:

=sum(<School={'Nursing'}>} Score) / sum(<Total School> Score) which would give Nursing % wrt to total.

This is what I am trying to say...

Re: ratio calculation

sunny_talwar — Thu, 31 Dec 2015 11:19:50 GMT

Balraj, there are couple of things

1) Your syntax for the expression is not right:

=Sum({<School={'Nursing'}>} Score) / Sum(Total <School> Score)

2) I am not sure which object would need this expression? Would you be able to implement your expression into previously attached qvw?

Thanks,

Sunny

Re: ratio calculation

Anonymous — Thu, 31 Dec 2015 11:36:02 GMT

Oops, wrong syntax...sorry for the inconvenience.

I was saying like this:

=Sum({<School={'Nursing'}>} Score) / Sum(Total Score)

Re: ratio calculation

sunny_talwar — Thu, 31 Dec 2015 11:44:33 GMT

Balraj Ahlawat wrote:

=Sum({<School={'Nursing'}>} Score) / Sum(Total Score)

Show an implementation of this then. I still don't think you need TOTAL in denominator.