Delete Blank Rows in Table Chart
Hello,
We have a drill down to a table chart. However it is showing rows that are blank and have no data to show. Is there any beast mode or alternative to get rid of this without scrubbing the data down since there are thousands of lines? I have attached an example of the table and what would need to be deleted (everything booked and GM) that has no data. Thanks!
Comments
-
I think a different way might be the more proper way but this seemed to work. I recreated your dataset and added an extra one that had data in those last 4 cells
This formula assumes you DO WANT TO SHOW the row when any of the 4 values exist. Create the below Beastmode then use that beastmode as a filter where the criteria is that the field is >=0
(CASE
WHEN `2016-17 Booked Revs` IS NOT NULL THEN `2016-17 Booked Revs`
WHEN `2016-17 Booked Costs` IS NOT NULL THEN `2016-17 Booked Costs`
WHEN `2016-17 GP` IS NOT NULL THEN `2016-17 GP`
WHEN `2016-17 GM` IS NOT NULL THEN `2016-17 GM`
END
)
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**0 -
Hi GuitarHero23,
Thanks, I did take your suggestion ans worked it with our current beast modes for 2016-17 revenue, costs, GM & GP. The last two are tricky since if GM or GP IS NOT NULL then it need to be the beast mode for th GM & GP as it's replacement since these two were manually calculated (not in the dataset).
In addition, if I plug in what I have according to your suggestion, it states that I can't use aggregations in filter. Any other round abouts to get rid of the empty rows that no data exists for?
(CASE
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) IS NOT NULL THEN `Booked Revenues CY`
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End) IS NOT NULL THEN `Booked Costs CY`
WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)) WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
)
END
)0 -
Hi Thank you for you reply/solution. It's a bit more complicated since two of the four columns calculated are through beast modes. So if NULL I need to replace with that same beast mode. Unsure if this causes some circular error. The below formula was validated however when putting in as a filter, an error came up that filters can't have aggregations. Thoughts or ideas to get rid of theses empty rows with no data? Thanks!
(CASE
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) IS NOT NULL THEN `Booked Revenues CY`
WHEN (CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End) IS NOT NULL THEN `Booked Costs CY`
WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
) WHEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
) IS NOT NULL THEN SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End) -
(CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Costs CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Costs CY`
End)
)
/
SUM((CASE
when (`Closing Year`='2016') and (`Closing Month`='12') then `Booked Revenues CY`
when (`Closing Year`='2017') and (`Closing Month`='8') then `Booked Revenues CY`
End)
)
END
)
0
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 257 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 501 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 378 Beast Mode
- 20 Variables
- 486 Automate
- 104 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 317 Distribute
- 65 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 64 Community Announcements
- 4.8K Archive