Nested If

[Chris Fries]

Having trouble with a calculated field. I need a formula that will for each "Patient Account" field, look at billed status and if thestatus equals "Closed" then return the actual payment. If the account status is anything other than closed return the expected payment field. My problem is that each "Patient Account" could have multiple lines and the payment is the same for each line. Meaning we could charge out for 50 lines, but there is only one payment to cover them all. I do not want it to sum the payments.

Received the following help, but how to I update for the Account status check

If(Min(RowId()) over ([Patient Account Number])=RowId(),MAX([Actual Pmt]) OVER ([Patient Account Number]),0)

[Colin Gray 3]

Hi there,

 

I think you can use a CASE statement to do this (but correct me if I have misunderstood your question / data). So for this example I added a 'Status' column to your test data as the example didn't include one, so change the Status column name to whatever you data is to get this working:

 

case

when [status]="Closed" then Max([Actual Pmt]) Over ([Patient Account Number])

ELSE

Max([Expected Pmt]) Over ([Patient Account Number])

endSo what this does is: when Status is closed - take the max actual payment per patient number, for all other scenarios, take the max expected payment. You can easily change it be SUM, AVG, MIN etc also if need be.

 

You could do this with just IF statementsbut I find the CASE statement easier to write and read for this scenario.

 

Hope this helps.

 

Thanks

Colin

[Chris Fries]

This almost works, but I am getting result for ever line item.  If I have multiple line items for a patient account then the actual or expected payment is repeated for each line item, but I only want my calculated column to return the value for one, not each.

 

 

 

Example:

 

Patient Account Number	Actual Pmt	Expected Pmt	Acct Status	Net Revenue
110159141	32,565.59	32,565.59	Closed	32,565.59
110159141	32,565.59	32,565.59	Closed
110159141	32,565.59	32,565.59	Closed
110159141	32,565.59	32,565.59	Closed
110159141	32,565.59	32,565.59	Closed
110159141	32,565.59	32,565.59	Closed
110159141	32,565.59	32,565.59	Closed
110203046	15,326.46	15,612.23	Closed	15,326.46
110203046	15,326.46	15,612.23	Closed
110203046	15,326.46	15,612.23	Closed
110203046	15,326.46	15,612.23	Closed
110203046	15,326.46	15,612.23	Closed
110212424	32,565.59	34,160.87	Closed	32,565.59
110250677	6,506.46	13,030.92	Billed	13,030.92
110250677	6,506.46	13,030.92	Billed
110250677	6,506.46	13,030.92	Billed
110250677	6,506.46	13,030.92	Billed
110250940	37,926.22	38,673.71	Closed	37,926.22
110251973	10,646.33	12,204.56	Billed	12,204.56
110252295	55,084.12	58,669.35	Billed	58,669.35
110252295	55,084.12	58,669.35	Billed
110252295	55,084.12	58,669.35	Billed
110252295	55,084.12	58,669.35	Billed
110252295	55,084.12	58,669.35	Billed
110252846	4,566.94	4,566.94	Closed	4,566.94

[Colin Gray 3]

I understand. When using over statements like this you end up with a result per row. You then can either handle it later on in visuals (by just taking the max, using the first expression for example), you can create a pivot table to reduce the table to single rows per customer for example, use cross tables etc. 

 

However, to do what you want is also possible as a CASE or nested IF. Here are both:

 

case  
when [status]="Closed" then If(RowId()=Min(RowId()) over ([Patient Account Number]),Max([Actual Pmt]) Over ([Patient Account Number]),NULL)
ELSE 
If(RowId()=Min(RowId()) over ([Patient Account Number]),Max([Expected Pmt]) Over ([Patient Account Number]),NULL)
end

 

and as a nested IF:

 

If(RowId()=Min(RowId()) over ([Patient Account Number]),if([status]="Closed",Max([Actual Pmt]) Over ([Patient Account Number]),Max([Expected Pmt]) Over ([Patient Account Number])))

 

I actually take back what I said and prefer the nested IF here now over the CASE statement. This is becuase I always want to avoid code repitition. In the case statement I have to check the rowid in two places, whereas with a nested IF, I only have this code once.

 

My only note of caution with this is that becuase you only have the result on one row, be careful of filters. For example, if you have a filter that allows someone to filter to a row that isn't the first for each patient number, they wont see any data for your new calculated column.

 

Hope this helps

 

Colin

 

 

[Colin Gray 3]

If you want to leave the table as is but use it for your visuals, I think the easiest thing to do is to create a copy of this table and apply a pivot transformation. So by pivoting on patient id and number and then taking the max of your cost columns, you can then add a simple calculated column to the pivot table with an IF to pick which value to show.

 

That way this table only ever has one row per patient. Producing visuals from this is then easier.

 

If you did not want to create a pivot of your table, you would have to apply aggregations on all charts such as First or Max so only one value from each patients costs was shown. 

 

In my experience however, if you are working on a dataset that will form the basis of your analysis, it is worth the effort to transform it into the ideal format for you to do your analysis before doing any visuals etc. Helps prevent mistakes and saves time in the long run normally.

 

Hope this helps

[Chris Fries]

Sorry, I didn't think about a user applying filters that would negate the calculated column I was attempt to get.  So if I leave as is per your original suggestion, how would I handle in a visualization so that a filter would no give incorrect results  When trying to apply the logic described I get an error.  

[Chris Fries]

Could you walk me through completing the pivot transformation as suggested  I'm not sure this is the answer, since it will be reducing the data down to a single line per account