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.
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.
Comments
-
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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thanks for the suggestion! I will give this a try and let you know how it turns out. Laurie
0 -
Hi @LLucinski
Were you able to get this to work?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
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.
0 -
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`
ENDYou can then wrap that in a COUNT aggregate function to get the number of discharge line items.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive