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

Reply
White Belt

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

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!

Highlighted
Black Belt

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

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
______________________________________________________________________________________________
White Belt

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

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!

 

Black Belt

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

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.5.pngQuantity by State

 

6.pngSales $ distribution with State Quick filter

 

7.pngRelationship 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
______________________________________________________________________________________________
White Belt

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

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!

Announcements
We want your feedback to improve the Dojo Community! If you are coming to the Dojo Award Ceremony next week Click here to learn more!