topic Re: Counting sum in variable in QlikView

Counting sum in variable

Wed, 13 Feb 2013 10:29:46 GMT

Hi all

I'm working on a dataset with student applications to a University where we have 66 different types of educations (Biology, Law, Economics etc.).

For that I need to make a Straight Table (preferably) where I can calculate the following:

A Count of Number of Educations this Year that have: '0' applications, '1-10' applications, '11-30' applications, '31-100' applications and '>100' applications.

I'm able to count the number of educations

(Count(DISTINCT {1<Year={$(vCurrentYear)}>} [Education]) )

But not the number of Educations that have '0' applications, '1-10' applications and so forth.

Can anyone help me on this please

Thanks in advance

Cheers

Bruno

Re: Counting sum in variable

SunilChauhan — Wed, 13 Feb 2013 10:55:52 GMT

in script

if(applicant fieldname=0,0,

if(applicant fieldname>0 and applicant fieldname<=10,'1-10',

if(applicant fieldname>10 and applicant fieldname<=30,'10-30',

if(applicant fieldname>30 and applicant fieldname<=100,'10-30',

if(applicant fieldname>100,'>100'))))) as youfieldname

and take this youfieldname in dimension and drag it tohorizontally

hope this helps

Re: Counting sum in variable

Wed, 13 Feb 2013 11:40:03 GMT

Hi Luminary

Thanks for your quick reply, I can't get it to work though.

I've done the following in the script:

if(Education=0,'0',

if(Education>0 and Education<=10,'1-10',

if(Education>10 and Education<=30,'11-30',

if(Education>30 and Education<=100,'31-100',

if(Education>100,'>100'))))) as EducationCountGrouped,

Can you see what I'm doing wrong?

I've attached a cleaned copy of my data in the original post so you can see it

Thanks

Bruno

Re: Counting sum in variable

SunilChauhan — Wed, 13 Feb 2013 11:43:57 GMT

its looking good to me

but dont know why its not working

Please attached file stating requirement again without any gap if possible

hope this helps

Re: Counting sum in variable

Wed, 13 Feb 2013 13:18:35 GMT

Hi Luminary

I'm not sure I expressed myself appropiate in my original post. I'm sorry, I'll try again:

The thing i'm looking for is the following:

I have a dataset with app. 10.000 rows, 1 for each person who applied to an Education on our University. We have 66 different Educations an applicant can choose from, f.ex. Biology, Law, Medicin and Economics. So if there is 29 applicants for Biology the dataset have 29 rows under the Education 'Biology' (1 for each person who applied).

I need to make a table that can summarize how many Educations that have had 0 applications, 1-10 applications, 11-30 applications 31-100 applications and >100 applications. F.ex. Biology recieved 29 applications, Economics 71 applications, Law 201 applications and Medicin 1.504 applications.

Based on these 4 Educations, my table would need to look like this:

0 app. 1-10 app. 11-30 app. 31-100 app. >100 app.

0 0 1 1 2

if this is also what you understood in the first place, what do I need to write in my expression in my table? I can't' figure it out

Thanks a lot

Bruno

Re: Counting sum in variable

SunilChauhan — Wed, 13 Feb 2013 16:09:51 GMT

see the attched file

hope this helps

Re: Counting sum in variable

Thu, 14 Feb 2013 07:29:39 GMT

Thanks for your effort.

I still can't get it to work though. The problem is that I do not have aggregatet data (for data see attachment in original post). I get all my Educations within 1-10, because I have a variable I'm counting on (CountVar) that equals 1.

Your TEST1 loads the data aggregatet as far as I can see.

It's probably a little thing, but I can't' figure it out.

Thanks

Bruno

Re: Counting sum in variable

Gysbert_Wassenaar — Thu, 14 Feb 2013 10:10:02 GMT

See attached qvw

Re: Counting sum in variable

Thu, 14 Feb 2013 10:41:08 GMT

This is exactly what I was looking for - thanks a lot

Bruno