topic Re: Complex Set Analysis causing null dimension row in chart in QlikView

Complex Set Analysis causing null dimension row in chart

chriscammers — Fri, 19 Sep 2014 14:39:49 GMT

I am working with a company who is analyzing medical claims data.

They want to show a chart counting the days where a member had an Outpatient ER visit. The visit should not result in an inpatient stay. So to identify the er visit I have three conditions.

Condition 1. Place of Service code on a line of the claim = 23

Condition 2. Revenue codes are in a list of codes for ER

Condition 3. Procedure codes are in a list of codes for ER

AND

Condition 4. The claim does not have any inpatient lines specified by Place of Service 21

When the parenthesis and stuff are placed correctly it would be like this...

(condition 1 OR Condition 2 OR Condition 3) and Condition 4

So I am showing a list of members and a count of the number of days that the member presented at the ER.

I am using the following expression

the Claim_ID identifies a collection of service lines so the conditions shown below are valid if on any service line

count(

	Count({
		(
		<Claim_ID = P({<POS_Code = {'23'}>})> +
		<Claim_ID = P({<RevenueCode = {$(vERRevenueCodes)}>})> +
		<Claim_ID = P({<CPT_HCPCS_Code = {$(vERProcedureCodes)}>})>
		)
		* <Claim_ID = E({<POS_Code = {'21'}>})>
	} Distinct MemberDateString

So finally the problem, the expression seems to return the right counts but the table will have a row with a ridiculous count and all the dimensions will be null. Is there something about this technique that is causing this, I have tried many variations of this but the null row persists. One important note, if I don't do the inpatient exclusion(POD_CODE = 21) then the null row does not appear.

Thanks for your help

Chris

Re: Complex Set Analysis causing null dimension row in chart

marcus_sommer — Fri, 19 Sep 2014 19:21:55 GMT

You nested aggregation-functions - this is only possible if you wrapped these functions in an aggr() - have a look here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/19/function-classes

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-used

- Marcus

Re: Complex Set Analysis causing null dimension row in chart

chriscammers — Sun, 21 Sep 2014 01:10:36 GMT

Oh for Pete's sake!

That was a typo, there is only one aggregation...

Count({
		(
		<Claim_ID = P({<POS_Code = {'23'}>})> +
		<Claim_ID = P({<RevenueCode = {$(vERRevenueCodes)}>})> +
		<Claim_ID = P({<CPT_HCPCS_Code = {$(vERProcedureCodes)}>})>
		)
		* <Claim_ID = E({<POS_Code = {'21'}>})>
	} Distinct MemberDateString

Re: Complex Set Analysis causing null dimension row in chart

marcus_sommer — Sun, 21 Sep 2014 08:56:49 GMT

It's difficult to say what could be wrong with such complex expression. It could be a small syntax error maybe with the variables or a logical issue or a technical problem (according to the set analysis help it could be give unexpected results by using from operators if the fields are from different tables).

I would these expression split in single parts, validate the results and combine them step by step.

- Marcus

Re: Complex Set Analysis causing null dimension row in chart

AlexOmetis — Thu, 11 Feb 2016 00:48:05 GMT

Did you ever get to the bottom of this by the way? I am facing the same issue...

Re: Complex Set Analysis causing null dimension row in chart

swuehl — Thu, 11 Feb 2016 09:17:21 GMT

What if you do it like

Count({
		< Claim_ID = P({<POS_Code = {'23'}>}) *
		P({<RevenueCode = {$(vERRevenueCodes)}>}) *
		P({<CPT_HCPCS_Code = {$(vERProcedureCodes)}>}) * E({<POS_Code = {'21'}>}) >
	} Distinct MemberDateString)

It's not easy to tell why you get a certain result with a set expression without knowing your data model.

Besides this, have you tried Option 'Supress When Value is NULL' on Dimension tab for your dimensions?

edit:

Just noticed that I am answering to an 2 years old post.