Services rendered in Days -90. Warning: I still suck at formulas!

[MaryCann]

I hope I can explain this...  Okay, I am trying to find out what non-medical services were rendered during the prior 90-days to Prescription start date.  Here's what I have so far:

The False at the left is pulling out non-medical services.  It later joins to the medication data.  I'm using this formula to determine prior 90-days:

Next, what is the formula I should use to figure out which services were provided during this time period?

[Luke_C]

Hi @MaryCann 

It sounds like you may want to use a filter tool with a custom filter like:

[Prescription Start Date] >= [90-Days Prior to Rx Start Data]

You could also bypass the formula you have and simplify your workflow by doing the below in a filter tool:

[Prescription Start Date] >= DateTimeAdd([Prescription Start Date],-90,'days')

[DataNath]

Do each of your records have a date field for when the non-medical service was rendered/issued? If so, you could:

• Append this new formula (90-days prior…) to each of the non-medical records from your false filter anchor

• Use a filter with the condition that (date of non-medical service issued) => (90-days prior…)

• Use a summarize tool on the true anchor of that filter, group by non-medical services

If your data is structured differently or doesn’t have a date then we may need some more info/screenshots or a sample flow!

[MaryCann]

@DataNath ...  Okay, I've got this:

But, I would like a formula to pull into Tableau with a column header "List of Services Rendered 90-Days Prior to Prescription Start Date"

How would I build that in?

And - THANK YOU in advance!

[DataNath]

From your Summarize tool there, is that the list you’re expecting?

If so, you can set the name of the output column to whatever you want in the Summarize tool configuration, right next to where the ‘group by’ dropdown is. After the Summarize tool you could just put an Output Data tool and output this list to a .hyper file (optimised for Tableau) and then bring that in as another data source in your Tableau workbook.

[MaryCann]

@DataNath ...  but, which Output Field Name do I change?  Oh - and I already have 7 (UGH!) output files going into Tableau - I don't want anymore...  it's already on the verge of too confusing!  It has to combine back into the tableau hyper extract...  Here's where I'm at:

Thanks DataNath!

[DataNath · Accepted solution]

Oh sorry, I thought you meant you just had one field which was a list purely of the services! I think in this case, the best approach would be to keep your output as is and do this in Tableau, especially to avoid extra data sources.

I’m not sure how your data is structured. However, if possible, I’d advise building the sheet you want and then adding 2 things to your ‘filter’ panel:

1) Non-medical services (hopefully this is just a discrete field where you can just tick non-medical from a few categories?)

2) Create a calculated field using the datediff function to work out the number of days since prescription, and use a dynamic range filter to include anything >= 90?