Count if less than 30.

[erickd1190]

Hi all,

Still a bit of a newbie when it comes to a lot of this stuff so bear with me.

I need to set a KPI to count how many of my records are completed within 30 days.

To complete this I wrote the following expression:

COUNT({$<visittype={"Strengths and Needs"}>}if((today()-visit_day<30),1,0))

However, instead of returning the count of records within 30 days, it counts everything.

For example: It should return around 1000, but is returning close to 4000.

How can I restructure this Chart expression to calculate correctly?

Any wisdom is greatly appreciated,

Erick

[rwunderlich · Accepted solution]

Your count() will count both the 1s and the 0s. If you change the Count() to Sum(), you should be okay.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[erickd1190]

Thank you Rob! I had just noticed that myself.

Talk about a rookie mistake.

All the best,

Erick

[rwunderlich]

I make those rookie mistakes all the time myself.

As an aside, if you coded the count like this:

COUNT({$<visittype={"Strengths and Needs"}>}if((today()-visit_day<30),1))

That is, null for the else part -- I believe you would get the correct answer as the nulls would not be counted. I prefer sum() because it seems less mistake prone to me,

-Rob