MoM Variance in Pivot
Hi All
is there a way to display the MoM Variance values in a pivot?
for context i have data per day for Company, Division, Subdivision hierarchy,
i can easily display the values at the end of the month in the hierarchy state but what i would like to do is calculate/display the monthly movement with the ability to drill down the hierarchy
i know there are various Period over period visuals but im yet to find one which solves
Best Answer
-
@Dverdon As @MichelleH says, pivot tables won't allow you to calculate in between columns and a Mega Table with a Beast Mode might be your best bet, as you won't need to do much to transform your data prior, although you'll lose the drill down capabilities you have in a Pivot Table (in case that matters to your case).
Another option would be to use an ETL to have the displaced value listed as well (Using
DATE_ADD(`Date`,INTERVAL 1 MONTH)
), there would be basically 2 ways to achieve this, either with APPEND or JOIN.The Append route is simpler although it will require you to store more rows, you'll need to identify the values with an additional column with either a value of 'Current' or 'Displaced' and then in your beast modes you'll use:
Current value
CASE WHEN `Identifier` = 'Current' THEN `Value` END
Variance
CASE WHEN `Identifier` = 'Current' THEN `Value` WHEN `Identifier` = 'Displaced' THEN -1*`Value` END
And you'll add these to your Pivot Table as values, selecting SUM as the aggregation type (if you want % of change you'll need to do the aggregation and division as part of your beast mode).
Keep in mind that when adding 1 month, any date that would fall outside the next month, becomes the last of the month instead (Jan 29, Jan 30, Jan 31 both become Feb 28/29 for example).
1
Answers
-
@MichelleH Thank you, so something along the lines of the below - Figures at each level of the Hierarchy (company) showing values and monthly movements, ideally then drilling down the Hierarchy showing the breakdown by next layer (division)
0 -
@Dverdon As @MichelleH says, pivot tables won't allow you to calculate in between columns and a Mega Table with a Beast Mode might be your best bet, as you won't need to do much to transform your data prior, although you'll lose the drill down capabilities you have in a Pivot Table (in case that matters to your case).
Another option would be to use an ETL to have the displaced value listed as well (Using
DATE_ADD(`Date`,INTERVAL 1 MONTH)
), there would be basically 2 ways to achieve this, either with APPEND or JOIN.The Append route is simpler although it will require you to store more rows, you'll need to identify the values with an additional column with either a value of 'Current' or 'Displaced' and then in your beast modes you'll use:
Current value
CASE WHEN `Identifier` = 'Current' THEN `Value` END
Variance
CASE WHEN `Identifier` = 'Current' THEN `Value` WHEN `Identifier` = 'Displaced' THEN -1*`Value` END
And you'll add these to your Pivot Table as values, selecting SUM as the aggregation type (if you want % of change you'll need to do the aggregation and division as part of your beast mode).
Keep in mind that when adding 1 month, any date that would fall outside the next month, becomes the last of the month instead (Jan 29, Jan 30, Jan 31 both become Feb 28/29 for example).
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive