# 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

Thanks

Suman

• Contributor

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,

1. Collapse the 'Addition' & 'Depriciation' columns and give a suitable name to the new columns using 'Collapse Column' Tile
2. Uncollapse Date to make it in columns instead of rows using uncollpase tile.
3.
* 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'.
4. Connect the output dataset. use this new dataset to create the card.
5. 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
6. 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.

'Happy to Help'

• Contributor

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,

1. Collapse the 'Addition' & 'Depriciation' columns and give a suitable name to the new columns using 'Collapse Column' Tile
2. Uncollapse Date to make it in columns instead of rows using uncollpase tile.
3.
* 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'.
4. Connect the output dataset. use this new dataset to create the card.
5. 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
6. 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.

'Happy to Help'
• 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

• Contributor

Hi Suman,

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.

'Happy to Help'
• 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.

Thanks

Suman

• Contributor

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.

'Happy to Help'
• Contributor

@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"
• 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!!

This discussion has been closed.