Create a crosstab or table chart like below with variance
Hi Dojo Team,
I'm new in domo and started to explore it. I need your help while creating a crosstab chart or Table chart like below image.
I have three column which inlcude Date, Additions, Depreciation. From all these three column, I want to calcualate variance between two months like below table.
Nov,19 | Sep,19 | Variance | |
Additions during the year | 394182139.1 | 321631631.2 | 72550507.8 |
Depreciation for the year | -433876572 | -352870676 | -81005897 |
Please help me grow, learn by putting your valuable ideas and suggestion on this question.
Thanks
Suman
Best Answer
-
HI @SumanK ,
As per the info provided by you, there are 3 columns Date, Addition and Depriciation.
Date Additions during the year Depreciation for the year Nov-19 394182139.1 -433876572 Sep-19 -433876572 -352870676 You can follow the steps to pivot the data in Magic ETL,
- Collapse the 'Addition' & 'Depriciation' columns and give a suitable name to the new columns using 'Collapse Column' Tile
- Uncollapse Date to make it in columns instead of rows using uncollpase tile.
-
* In this option, please add all the dimensions under 'Select the columns that identify a row' option to avoid data mismatch. In the example, we have only 2 dimensions, 'Date' and 'KPI'. - Connect the output dataset. use this new dataset to create the card.
- You can select a table under chart type , put KPI as the first column, followed by date columns (Nov 2019 and Sep 2019). Create a beast mode 'Variation' using the following formula
- This would create the graph as required.Though there are more efficient ways of creating such graphs. Here You can make it more dynamic by making Month of date independent from the year by creating a new Year column. You can also use Pivot Table (BETA) but the variation part would be tricky, hence I suggested regular 'Table' chart. Efficiency and best practices would develop eventually on the fly.
Please feel free to raise any queries you have.
Hope this helps.
Aditya Jain
'Happy to Help'1
Answers
-
HI @SumanK ,
As per the info provided by you, there are 3 columns Date, Addition and Depriciation.
Date Additions during the year Depreciation for the year Nov-19 394182139.1 -433876572 Sep-19 -433876572 -352870676 You can follow the steps to pivot the data in Magic ETL,
- Collapse the 'Addition' & 'Depriciation' columns and give a suitable name to the new columns using 'Collapse Column' Tile
- Uncollapse Date to make it in columns instead of rows using uncollpase tile.
-
* In this option, please add all the dimensions under 'Select the columns that identify a row' option to avoid data mismatch. In the example, we have only 2 dimensions, 'Date' and 'KPI'. - Connect the output dataset. use this new dataset to create the card.
- You can select a table under chart type , put KPI as the first column, followed by date columns (Nov 2019 and Sep 2019). Create a beast mode 'Variation' using the following formula
- This would create the graph as required.Though there are more efficient ways of creating such graphs. Here You can make it more dynamic by making Month of date independent from the year by creating a new Year column. You can also use Pivot Table (BETA) but the variation part would be tricky, hence I suggested regular 'Table' chart. Efficiency and best practices would develop eventually on the fly.
Please feel free to raise any queries you have.
Hope this helps.
Aditya Jain
'Happy to Help'1 -
Hi @Aditya_Jain , Thanks for your response. I have tried your way but kpi is getting repeated twice. Here I have attached two image. One after adding all component of ETL and second image of domo card where you can see kpi is getting repeated. How should I resolve this issue?
Please let me know if I did anything wrong.
Thanks Suman
1 -
Hi Suman,
Please follow the steps,
Click on the Sep, 2019 and Nov,2019 columns of you table card as depicted in the pic below and Select 'Sum' under the aggregation options. You are getting 2 sets of values because you have not aggregated it.
Hope this helps.
Aditya Jain
'Happy to Help'2 -
Hi @Aditya_Jain
I did that and repeated problem has been resolved but
Once I run for preview then value is perfect till collapse component but after adding and running Uncollapse component then value becomes very less.
Here I have attached three view.
1. this image is for collapse Component preview
2. This image is for uncollapse Component preview
You can see how number has been changed from large to very small.
3. This image is for uncollapse Component configuration.
Please check where I'm wrong.
Thanks
Suman
1 -
Hi Suman,
If there are only these 3 columns in your dataset, then conceptually what you have done should work. You might see data in not so okay form in the intermediate ETL steps as it becomes dis/un aggregated. I am sure if you plot the graph, you should get all the values in the expected form as you would aggregate (sum in your case) the data there. Do let me know if it does not work, we may need to see some more details about the data.
Hope this helps.
Aditya Jain
'Happy to Help'1 -
@SumanK - It's recommended to use a 'Group By ' function to aggregate after the collapse/uncollapse work. That will resolve your issue prior to the card building.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi @Aditya_Jain
I just did the same but without Uncollapse component and It solved my half problem but not variance problem. But Thank You for your time and help!!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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