Count + Agg

[#1]

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?

[#2]

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.

[JonnyPoole]

  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

[#4]

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! 

[JonnyPoole]

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... ?

[#6]

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)

[JonnyPoole · Accepted solution]

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 )

[#8]

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

[JonnyPoole]

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.

[#10]

No. This is perfect.