How do I translate these report format in Excel to Domo?

user02072
user02072 Member
in Magic ETL

Hi, 

I have these tables that looks like below:

exceltables.png

The formula shown is for J12, and going from J12:V16 they have the same syntax of formula like that. Other numbers are manually input or linked from other sheets. 

Now, how do I replicate the report format and the formula in Domo? I have tried using Beast mode to solve this, but so far I can only manage to do this for EACH cell in J12:V16 only, not the whole J12:V16. 

So anyone can kindly suggest me as how to replicate these into Domo using Beast Mode (if possible, but one formula for all cells), or ETL or some other kind of transformation? Thanks so much!

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    What does your data set look like?

     

    This is essentially a pivot table.  In order to perform these calculations, you will need to have a "flat" data file.

     

    In addition, Domo does not have a great way to present a pivot table like this.  I would argue that there could be much more intuitive ways to present this data.  When looking at this table, what questions are you wanting to answer?  Are you more interested in comparing quantity/sales/cost across different geographies, or accross different products?  

     

    If your final output absolutely has to be a pivot table in this format then I would suggest you keep it in excel.  


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02072
    user02072 Member

    Hi @ST_-Superman-_, thanks for the answer. 

    The dataset is the one that I posted in the question. So if this is the case then there's no way for Domo to replicate it like this? 

    Even by transposing or pivoting the columns? Because as you may have already seen, there are multiple dimensions included here. And in this case, all of them are important. 

    So do you have any other suggestions as how to build similar tables like this in Domo?

     

    Thanks!

     

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Hi @user02072

     

    If you want this data in Domo, my suggestion would be to find a way to get the data set to a flat file.  A flat file is going to be harder to analyze, but much easier to use in a relational database.  You want the data to look something like this:1.png

     

    You can then add a few case statments in a MySQL select statement to include your calculations or use these beastmodes to calculate the extra fields.3.png

     

    2.png

     

    I did mention that Domo does not have a lot of options when it comes to Pivot Tables.  But you can make a "Sumo" card that will pivot the data for you.4.png

     

     

     

    However, I would think a better way to analyze this data would be to discuss with your audience exactly what questions are being asked and answered with this data.  Then build out a series of cards that specifically answer each question.Quantity by StateQuantity by State

     

    Sales $ distribution with State Quick filterSales $ distribution with State Quick filter

     

    Relationship between Cost and Sales $ by State for ApplesRelationship between Cost and Sales $ by State for Apples

     

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user02072
    user02072 Member

    Hi @ST_-Superman-_, thanks for your quick reply. After reading your answer I think the best way there is now is to think about a workaround solution then. As for the data file format, it's a good start that I will try to apply to my data. 

    Thanks a lot again for your suggestions!

Categories