Healthcare Patient Product Order Transaction History: pick the primary Insurance Plan

Question

Hi,

I have a transaction history with following attributes:

* Date of Shipment
* Customer ID
* Order ID
* Product Item ID
* Sales Dollar
* Insurance Plan ID
* Referral Source ID

Over 6 months period, a customer can have multiple orders; each order can have multiple product items.  Most of the time, “Insurance Plan ID” and “Referral Source ID” are the same, but may also be different.

I want to choose “Insurance Plan ID” based on the largest value of “Sales Dollar” during this period.  Alternatively, I may want to choose “Insurance Plan ID” based on the date (first-ever “Date of shipment” during this period of 6 months).

Additionally and separately, I want to choose “Referral Source ID”, based on largest “Sales Dollar” at Order level, similar to “Insurance Plan ID” situation above.

How do I do that?  Thank you for your help.

jdunkerley79 · Accepted Answer

Yes perfectly doable.

For max dollar sales, use a summarise tool to create the total by customer and plan. You can then use a sort tool and sample tool to get one for each customer.

For the first plan, again a summarise tool will get the min date. You can then join back to the original data and again use a sample tool to get the insurance plan id.

Sample attached. You should be able to adjust it to for referral source id in a similar vain.