Bar Graph: % of total calculation in Beast Mode for a string field

Reply
Highlighted
Visitor

Bar Graph: % of total calculation in Beast Mode for a string field

I'm creating a horizontal bar chart that is returning the count for a field called "Issue Type". The "Issue Type" field can contain 1 of 20 issues (string). I can create a Percent of Total card that returns what I want, but I would like to create the metric (%) as a beast mode calculation to be shown in a Line+Grouped bar chart as the line. Since we have a card that can creat the metric, I'm sure it's possible in MySQL. This is what I have so far:

 

/**count(`Issue Sub-Type`) / **/
case
when `Issue Sub-Type` <> ''
then 0
END

 

This is resulting in 1 for all groups...or the equivalnt of just =count (`Issue Sub-Type`), which returns the same thing. 

 

Has anyone solved for this type of metric before? I have on another card, but the field was a 1/0 numaric field and used sum()/count(), which I can't do with a categorical string. 

Tags (2)

Accepted Solutions
Blue Belt

Re: Bar Graph: % of total calculation in Beast Mode for a string field

Hi, 

 

If I correctly understood the question. I think you will not be able to get this within a Beast Mode because Beast Mode operates at a record or group level depending on the card layout, in this way you can't get the total values from all groups in order to calculate the percentage.

You will have resort to a data flow in order to do this, with the downside of getting a fixed grouping (don't really know if this will impact your card as I don't know how you are planning the card grouping), a great idea could be using ETL with the new window functions.

 

Here a great link about ETL window functions:

 https://knowledge.domo.com?cid=etlactionsrankandwindow

 

Hope this helps.

Ricardo Granada 


MajorDomo@Lusiaves



**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.
Visitor

Re: Bar Graph: % of total calculation in Beast Mode for a string field

I ended up using the %_PERCENT_OF_TOTAL data label setting to add the label to the bar graph and using the hover text setting to show the count, or number of units of the total. I then added a text field based on the date I need to split up the data by and made it a "Display as Quick Filter", so the user can look at different date cuts. 

 

Ideally, I would like a field that is the % of population by status. That way, I can make a line graph showing the % of total and a bar graph showing a different metric besides units. 

 

Example data:

example.png

 

 

Really, it's trying to get around the one axis limit with a date quick filter. Thanks! 

 

EDIT: I forgot to say that the % fail rate I want to calculate inside of Domo if possible. 

 


All Replies
Blue Belt

Re: Bar Graph: % of total calculation in Beast Mode for a string field

Hi, 

 

If I correctly understood the question. I think you will not be able to get this within a Beast Mode because Beast Mode operates at a record or group level depending on the card layout, in this way you can't get the total values from all groups in order to calculate the percentage.

You will have resort to a data flow in order to do this, with the downside of getting a fixed grouping (don't really know if this will impact your card as I don't know how you are planning the card grouping), a great idea could be using ETL with the new window functions.

 

Here a great link about ETL window functions:

 https://knowledge.domo.com?cid=etlactionsrankandwindow

 

Hope this helps.

Ricardo Granada 


MajorDomo@Lusiaves



**If the post solves your problem, mark it by clicking on "Accept as Solution"
**You can say "Thank you" by clicking the thumbs up in the post that helped you.
Visitor

Re: Bar Graph: % of total calculation in Beast Mode for a string field

Thanks for the reply @RGranada,

 

Great Suggestion. That actually solves a different card I was working on, but I'm finding it difficult to use the grouping card and capturing the highest value for a constant column used to calculate the % of total rows.

 

I'm going to try and move from the ETL to calculate in MySQL. With the help of @ValiantSpur, I created a MySQL code that returns the count of records and created a constant column with the count added back to each record. So now I can take the population and divide it by the MAX(of `max_row`). 

 

SELECT COUNT(`Issue Sub-Type`) as `max_row` from pcqc_upload


SELECT `loan #`, `max_row`
FROM pcqc_upload
INNER JOIN testsql ON `Loan #` = `Loan #`

 

I then joined the data back to the original table, allowing for a % of total population.

 

This is allowing me to do it, but my ETL is becoming cumbersome. The next goal is going to create a column that has the count() as a constant in the column, but at month to create a timeseries that can be filtered. 

 

Any other feedback welcome! 

Tags (2)
Yellow Belt

Re: Bar Graph: % of total calculation in Beast Mode for a string field

Can you provide some basic input data and expected output?

 

Is the "total calculation" the number of total rows?

 

E.g.:

Issues

A

A

B

C

 

Total = 4

Issue A: Count = 2, Percentage = 50%

Issue B: Count = 1, Percentage = 25%

Issue C: Count = 1, Percentage = 25%

 

How does the total calculation need to behave with dates? Is the total the total for the entire dataset or only the total for the date filter selected?

Visitor

Re: Bar Graph: % of total calculation in Beast Mode for a string field

I ended up using the %_PERCENT_OF_TOTAL data label setting to add the label to the bar graph and using the hover text setting to show the count, or number of units of the total. I then added a text field based on the date I need to split up the data by and made it a "Display as Quick Filter", so the user can look at different date cuts. 

 

Ideally, I would like a field that is the % of population by status. That way, I can make a line graph showing the % of total and a bar graph showing a different metric besides units. 

 

Example data:

example.png

 

 

Really, it's trying to get around the one axis limit with a date quick filter. Thanks! 

 

EDIT: I forgot to say that the % fail rate I want to calculate inside of Domo if possible. 

 

Announcements
Domopalooza 2018! Early bird pricing is still available! Click here!