Uncollapse Columns or Dynamic Pivot or ??

Greetings - I'm working on a Finance Dashboard and I've been given a data set in Excel that pulls metrics from various source files via a VLOOKUP; this is updated monthly with current month and year to date values. I can create most of the cards I need from this data, however, I need to calculate a new metric. The problem is the way the file is set up -- see snapshot below. The two metrics that I need to use to produce the new metric are in the same column -- C.

FS Upload.PNG

I need to produce the Outpatient discharges which is Adjusted Adm/Disch - DISCHARGES. I thought about an ETL using UNCOLLAPSE COLUMNS, but I have 431 monthly metrics. Any thoughts on how to best proceed?

Thanks,

Laurie L.

Find more posts tagged with

Comments

GrantSmith

Hi @LLucinski ,

You could utilize a beast mode with a case statement to calculate the outpatient discharges

For example:

CASE WHEN `Filter Field` IN ('DISCHARGE VALUE1', 'DISCHARGE VALUE2') THEN `Metric Field` END

That will then only contain the metric you're utilizing if it's a discharge record. You'll also want to make sure you save the calculation on the dataset (click the checkbox in the lower right of the beast mode window) so you can reuse this field on other cards.

Screen Shot 2020-04-06 at 8.11.55 PM.png

LLucinski

Thanks for the suggestion! I will give this a try and let you know how it turns out. Laurie

GrantSmith

Hi @LLucinski

Were you able to get this to work?

LLucinski

No, I was not able to get it to work. I just couldn't get beyond the issue that the both of the metrics I needed to perform the operation were in the same column. I went back to the requester to ask that the metric be added to the source file.

I do appreciate your help though!

Thanks.

GrantSmith

The CASE statement I mentioned above should handle this scenario which will combine the metric into the same column if the stat types are different. What does your CASE statement look like?

It should look something like:

CASE WHEN `Line Item / Metric / Stat` IN
('DISCHARGES', 'Adjusted Adm/Disch') THEN
   `Line Item / Metric / Stat`
END

You can then wrap that in a COUNT aggregate function to get the number of discharge line items.