If statement. Need help

[Anonymous]

I work for a health system. We are trying to calculate new patient visits vs follow up visits.

The only variable we have to measure difference are CPT codes that are created each time a person comes in whether for a new visit or follow up. Unfortunately CPT codes for new patient visits are exclusive for new patients but follow up CPT codes could also go into new visits.

We tried this function.

If (match (CPT code '1111','2222' etc), 'new')

if not( match (CPT code '1111','2222' etc), 'follow up')

I am not big on coding so please excuse if this looks remedial.

The main goal is to find a statement that will

1. mark a patient visit NEW if the visit contains even 1 CPT code that is in the new list.

2. Will only mark it as a follow up visit if there are no new CPT codes within the visit.

Please let me know if you need more clarification. Any help would be very much appreciated.

Thanks

[Ricardo_Gerhard]

Hey Jay, let me sse if I understand your requirements. Just try to create a code like this:

- First, create a tmp table and select patients with group by who have 1 an 2 visits;

- Create a secondary table to store who have1 and mark with if statement;

- Create a thirty table who have 2 visits with the second if statement;

- Then, create a main table, loading this two tables residents creating;

I guess that may help you.

[agigliotti]

how is your pivot table (row,column,measure)?

what i wrote before is a measure not a dimension.

what's your expected result with pivot table ?

[Anonymous]

We're trying to run it as a calculated dimension.

So the requirement is this.

If a patient has a NEW CPT code within their visit, then we have to count them as NEW, even if they have CPT codes shown in the above pic as 'other"

The problem is that the match function is returning is correctly returning the 'new' CPT codes back as new patients, but its also counting the new as other because some  VISITS contains both CPT codes.

[agigliotti]

it's still unclear.

you should provide a sample app with mock data to try to help you.

[consultant_bi]

Hello,

try my solution based on calculated dimension , i implement an example from your explication and it done me this result

from cpt 111 until 116 is new and if else follow

my formula

=if(cpt='111','new',if(cpt='112','new',if(cpt='113','new',if(cpt='114','new',if(cpt='115','new',if(cpt='116','new','follow'))))))

[Anonymous]

We're trying to run it as a calculated dimension.

[agigliotti]

you could try to do:

as pivot row:

=Aggr( if( count( DISTINCT [CPT code] ) = 1, 'New',

if( count( DISTINCT [CPT code] ) > 1, 'Follow up' ) ), [CPT code] )

as pivot measure:

count( [CPT code] )

[Anonymous]

Thanks for the input John, I just emailed our BI guys to take a look at it. I'll let you know if this it! Much appreciated.

[zebhashmi]

if(Count(match(CPT code, List) )= 1,New,Follow up)

[Anonymous]

I gave it a try, was not successful. We are handing it off to the BI team to research it further. I think they will try to find a more granular association that is not dependent on CPT codes. Thanks for your help Andrea!