18 Replies Latest reply: Sep 26, 2014 10:25 AM by Nathan Horn

# Count + Agg

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

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

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

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

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

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

• ###### Re: Count + Agg

=if(OBSVALUE > 7, OBSVALUE)

• ###### Re: Count + Agg

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
AmyA1c20145.1
BryanA1c20148.2
GuyLDL2014150
CarolA1c201412.6
SarahLDL2014200
BrendaSmoke Cessation2014Yes

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

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.

to get this

• ###### Re: Re: Count + Agg

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

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

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

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

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

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.

• ###### Re: Count + Agg

No. This is perfect.

• ###### Re: Count + Agg

Hi ,

Try this Expression

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

• ###### Re: Count + Agg

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