Uncollapse Columns or Dynamic Pivot or ??

Reply
Highlighted
Yellow Belt

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?  

Discharges.PNG

Thanks,

 

Laurie L.

Highlighted
Brown Belt

Re: Uncollapse Columns or Dynamic Pivot or ??

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



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Yellow Belt

Re: Uncollapse Columns or Dynamic Pivot or ??

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

Highlighted
Brown Belt

Re: Uncollapse Columns or Dynamic Pivot or ??

Hi @LLucinski 

Were you able to get this to work?



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Yellow Belt

Re: Uncollapse Columns or Dynamic Pivot or ??

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.

Highlighted
Brown Belt

Re: Uncollapse Columns or Dynamic Pivot or ??

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Announcements
What is your Dojo rank and what badges have you earned? How am I doing? Get a refresher on how to up your rank and collect badges in the Dojo here.