Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

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 )


18 Replies
jerem1234
Valued Contributor II

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!

muniyandi
Contributor III

Re: Count + Agg

Hi ,

Try this Expression

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

Not applicable

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)


Bar qlik.JPG.jpg

Not applicable

Re: Count + Agg

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

Employee
Employee

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) )

Not applicable

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.

graph.JPG.jpg

Employee
Employee

Re: Count + Agg

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

Not applicable

Re: Count + Agg

=if(OBSVALUE > 7, OBSVALUE)

Employee
Employee

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.

Community Browser