Pivot table, unable to hide months with no value.
I would like to list all the month actuals first, then month's budget numbers, etc.. (ie. Jan actuals, Feb, Actuals, March Actuals. then Jan Budget, Feb Budget, etc.) Unfortunately I do not see a way to do this without creating a separate column for each of the months. This solution however does not allow me to hide columns we do not have financials for. All recommendations are welcome. (I know I can manually add/hide months every month in Analyzer but was hoping to avoid that and leaves room for manual error)
Currently it lists actual, budget, variance for each month together. This is not how i want it.
Best Answer
-
Similar to what you're saying, you could use an ETL to restructure your data to something like:
Type Index | Description | Month | Actual/Budget/Variance | Amount
To do that, you could:
- split your dataset using two filter tiles into actual and budget
- Join them back together on Type/description/month
- Use a formula tile to calculate variance. So you should now have something like:
- Type Index | Description | Month | Actual | Budget | Variance
- Dynamic Unpivot your data to stack the Actual/Budget/Variance on top of each other
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
Have you tried displaying your Actual / Budget / Variance and then the month in your pivot table so that the ordering is reversed?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Yes, so that would be ideal if my data was set up that way.. I understand what you are saying. I have actual and budget in the data so this works perfectly, but unfortunately Variance is a beast mode calculation. Doing it this way is perfect for Actual and Budget, but then I cannot have a variance number.
0 -
Similar to what you're saying, you could use an ETL to restructure your data to something like:
Type Index | Description | Month | Actual/Budget/Variance | Amount
To do that, you could:
- split your dataset using two filter tiles into actual and budget
- Join them back together on Type/description/month
- Use a formula tile to calculate variance. So you should now have something like:
- Type Index | Description | Month | Actual | Budget | Variance
- Dynamic Unpivot your data to stack the Actual/Budget/Variance on top of each other
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
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