Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: IF Statement (error)

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
5 Replies

Re: IF Statement (error)

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

Re: IF Statement (error)

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

-EK

Not applicable

Re: IF Statement (error)

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

Re: IF Statement (error)

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

Re: IF Statement (error)

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.

Community Browser