topic Re: Count + Agg in QlikView

Count + Agg

Thu, 25 Sep 2014 16:57:37 GMT

I need to get a count of how many A1c's with a value greater than 7 using an expression within a bar chart.

This is what I have thus far...

Count(NAME = 'A1c') and if(OBSVALUE > 7,OBSVALUE)

Of course this did not work for me. Any ideas?

Re: Count + Agg

jerem1234 — Thu, 25 Sep 2014 16:59:59 GMT

Try something like:

count({<NAME = {'A1c'}, OBSVALUE = {'>7'}>}NAME)

or to count only distinct names:

count({<NAME = {'A1c'}, OBSVALUE = {'>7'}>} distinct NAME)

Hope this helps!

Re: Count + Agg

muniyandi — Thu, 25 Sep 2014 17:01:55 GMT

Hi ,

Try this Expression

Count(if(NAME = 'A1c' and OBSVALUE > 7,NAME))

Re: Count + Agg

Thu, 25 Sep 2014 18:37:49 GMT

So I thought this was the right answer. My bar graph after adding in different users gives my only a value of 6. Looking at the records for A1c's, under a single user I can see at least 15 values over 7.

count({<NAME = {'A1c'}, OBSVALUE = {'>7'}>}NAME)

Re: Count + Agg

Thu, 25 Sep 2014 18:38:51 GMT

This expression gave me a count of over 250.... Way over 15.

Re: Count + Agg

JonnyPoole — Thu, 25 Sep 2014 19:05:55 GMT

This will conditionally count NAMES that have a NAME='A1c' and a summarized OBSVAUE (summing all detail records) greater than 7.

If your data only has 1 OBSVALUE per NAME entry, this is no different than before. But if your data has multiple records an you need to do a subtotal of OBSVALUE and compare if the subtotal is greater than 7, then this does that.

count( if ( aggr( sum(OBSVALUE), NAME) > 7 and NAME = 'A1c', , NAME) )

Re: Count + Agg

Thu, 25 Sep 2014 19:14:27 GMT

I have a name of a record called "A1c" which has a different value per date of service, per patient, per ordering provider. I'm trying to figure out how many patient's per provider have an A1c of >7.

Name Table = "A1c"

OBSVALUE table = "the value"

When I tried you expression it only gave me a count of one for a single provider. That provider actually has 11.

Re: Count + Agg

JonnyPoole — Thu, 25 Sep 2014 19:18:17 GMT

what is the expression used in the screenshot for 'Patients' ?

Re: Count + Agg

Thu, 25 Sep 2014 19:20:16 GMT

=if(OBSVALUE > 7, OBSVALUE)

Re: Count + Agg

JonnyPoole — Thu, 25 Sep 2014 19:24:39 GMT

hmm... i'd prefer not to guess . Can you upload some dummy data or a sample QVW ? There is a twist in there that a sample will clear up.

Re: Re: Count + Agg

Thu, 25 Sep 2014 19:37:59 GMT

User	NAME	Date	Value
Amy	A1c	2014	5.1
Bryan	A1c	2014	8.2
Guy	LDL	2014	150
Carol	A1c	2014	12.6
Sarah	LDL	2014	200
Brenda	Smoke Cessation	2014	Yes

I can't send what I have do to HIPAA (medical law) but, I put this example together which I hope helps.

Re: Re: Re: Count + Agg

JonnyPoole — Thu, 25 Sep 2014 19:51:16 GMT

was afraid of that. Here is used this expression:

count (if( NAME = 'A1c' and Value > 5, NAME))

Attached is the app for your reference. its an inline load so you can change the data if you need to replicate the issue.

let me know your thoughts

to get this

Re: Re: Count + Agg

Thu, 25 Sep 2014 20:08:16 GMT

The statement makes sense to me however, my results are "no data." I've been lost on this one for some time. I appreciate all your help!

Re: Re: Count + Agg

JonnyPoole — Thu, 25 Sep 2014 20:18:16 GMT

Are your results 'no data' with my sample or your QVW ? I believe the sample works for you just wanted to check.

Are you able to share your QVW ? or some reduced or scrambled version ? There is a scramble feature on the document properties and you could load much reduced data by altering hte load script and removing fields and adding where filters... ?

Re: Re: Count + Agg

Thu, 25 Sep 2014 20:48:19 GMT

I tried going this route (see below) and it looked closer to what I was after however, the count is not correct. The table chart is the total number of actual records where the bar chart is .... well.... a much higher number.

I added the value into the dimension list and used the expression below.

expression: count({<NAME = {'A1c'}, OBSVALUE = {'>7'}>}NAME)

Re: Re: Count + Agg

JonnyPoole — Fri, 26 Sep 2014 00:07:41 GMT

hmmm... i wonder if its double or triple counting the records. Whichever way you try whether its a count(if () ) or a count( {SET} ) , try adding a DISTINCT in there and possible concatenating the NAME with the VALUE:

Test 1: count( distinct {<NAME = {'A1c'}, OBSVALUE = {'>7'}>}NAME)

Test 2: count( distinct {<NAME = {'A1c'}, OBSVALUE = {'>7'}>} NAME&OBSVALUE )

Re: Re: Count + Agg

Fri, 26 Sep 2014 13:44:16 GMT

Genius! Test 2 worked like a charm. All the records are matching up! Thank you so much for help me on this!

Re: Count + Agg

JonnyPoole — Fri, 26 Sep 2014 13:47:47 GMT

ok. thats going to count each unique combination of name and obsvalue.

Would you ever need to count a single combination of NAME and OBSVALUE more than once ? If the answer is no, then you should be good.

glad we made progress.

Re: Count + Agg

Fri, 26 Sep 2014 14:25:22 GMT

No. This is perfect.