Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF Statement (error)

Hello,

I am thinking this is an easy fix-

Count Concatenate

IF(AMOUNT>0, Count(DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])), 0)

I can see the individual counts working, but the total shows 0. Any idea why this is happening?

Screenshot.gif

~EK

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You probably have to aggregate over your dimensions.

aggr(IF(AMOUNT>0, Count(DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])), 0), dim1,dim2)

If you use a straight table you could set the Total Mode of the expression to Sum of Rows.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

You probably have to aggregate over your dimensions.

aggr(IF(AMOUNT>0, Count(DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])), 0), dim1,dim2)

If you use a straight table you could set the Total Mode of the expression to Sum of Rows.


talk is cheap, supply exceeds demand
Not applicable
Author

As easy as switching the total mode. Thanks for the extra set of eyes!

-EK

Not applicable
Author

Hi Gysbert,

Wondering if you could help with making this IF statement a little more complicated. I have 2 procedure codes that would skew my data. 99291-is a code listed for 30-74 min of a hospital visit. Once the visit exceeds that time-99292 would be used to show that an additional 30 min was needed for the visit and so on. My current concatenation would count that twice as they are different [Proc Code]s. Do you know what to add so that proc codes 99291 and 99292 are only counted once if listed on the same day?

-EK

Gysbert_Wassenaar

Try this:

Count({<AMOUNT={'>0'},[Proc code] -={'9929*'}>+<AMOUNT={'>0'},[Proc code]={'99291'}>} DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID]))

It should count all records where AMOUNT > 0 and Proc code doesn't start with 9929, unless it's 99291.


talk is cheap, supply exceeds demand
Not applicable
Author

Hmmmm still doesnt seem to be recognizing the formula. When i paste this formula in- {'9929*'}>+<AMOUNT={'>0'},[Proc code]={'99291'}>} DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])) is highlighted in red. I can imagine this is a little difficult when you are not working with real fields. I don't own this app either so not sure how I could send you a template version.

If i get rid of the negative sign before the equal sign -={"9929*} it likes the formula, although ithat changes the formula to include vs exclude.