Create a crosstab or table chart like below with variance

SumanK
SumanK Member
in APIs & Domo Developer

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,19Sep,19Variance
Additions during the year394182139.1321631631.272550507.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

  • Aditya_Jain
    Aditya_Jain Contributor
    Answer ✓

    HI @SumanK ,

     

    As per the info provided by you, there are 3 columns Date, Addition and Depriciation.

    DateAdditions during the yearDepreciation for the year
    Nov-19394182139.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' Tileimg1.png
    2. Uncollapse Date to make it in columns instead of rows using uncollpase tile.
    3. img2.png 
      * 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.img3.png
    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 formulaimg4.pngimg5.png
    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

    'Happy to Help'

Answers

  • Aditya_Jain
    Aditya_Jain Contributor
    Answer ✓

    HI @SumanK ,

     

    As per the info provided by you, there are 3 columns Date, Addition and Depriciation.

    DateAdditions during the yearDepreciation for the year
    Nov-19394182139.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' Tileimg1.png
    2. Uncollapse Date to make it in columns instead of rows using uncollpase tile.
    3. img2.png 
      * 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.img3.png
    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 formulaimg4.pngimg5.png
    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

    'Happy to Help'
  • SumanK
    SumanK Member

    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?

    1.PNG

    2.PNG

    Please let me know if I did anything wrong.

     

    Thanks Suman

  • Aditya_Jain
    Aditya_Jain Contributor

    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.img6.png

     

    Hope this helps.

     

    Aditya Jain

    'Happy to Help'
  • SumanK
    SumanK Member

    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

    1.PNG

     

    2. This image is for uncollapse Component preview

    2.PNG

    You can see how number has been changed from large to very small.

     

    3. This image is for uncollapse Component configuration.

    3.PNG

     

     Please check where I'm wrong.

     

    Thanks

    Suman

     

  • Aditya_Jain
    Aditya_Jain 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.

     

    Aditya Jain

    'Happy to Help'
  • DataMaven
    DataMaven Coach

    @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"
  • SumanK
    SumanK Member

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

This discussion has been closed.

Categories