Create a crosstab or table chart like below with variance

SumanK

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

Aditya_Jain

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.

 

Aditya Jain

Aditya_Jain

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.

 

Aditya Jain

SumanK

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

Aditya_Jain

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

SumanK

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

 

Aditya_Jain

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

DataMaven

@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.  

 

 

SumanK

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!!