Beast Mode Help: Calculating Hit Rate (Projects & Quotes)
Here is a short video overview of what I am trying to accomplish: https://youtu.be/Dz_uXS1OGco
In simple terms the Project Hit Rate is the Count of (distinct) "Project Name" where "Convereted to Order?"='Yes' divided by the total Count of (distinct) "Project Name".
In simple terms the Quote Hit Rate is the Count of (distinct) "Quote Number" where "Convereted to Order?"='Yes' divided by the total Count of (distinct) "Quote Number".
A project might consist of multiple quotes, if any 1 of the quotes has "Convereted to Order?"='Yes' then the project is counted as a won project in the calculation above. Project Hit Rate = 100%
In the case of quotes, if say there are 4 quotes on the project and only one of them is "Convereted to Order?"='Yes' then the quote hit rate is 25%
My current Beast Modes:
Project Hit Rate:
CASE WHEN IFNULL(COUNT(DISTINCT `Project Name`),0)=0 THEN 0 ELSE CASE when `Converted to Order?`='Yes' then COUNT(`Project Name`) else 0 END / COUNT(DISTINCT `Project Name`) END |
Quote Hit Rate:
CASE WHEN IFNULL(COUNT(`Quote Number`),0)=0 THEN 0 ELSE CASE when `Converted to Order?`='Yes' then COUNT(`Quote Number`) else 0 END / COUNT(`Quote Number`) END |
I should note I am using subtotals in the table card, and the subtotals are not working. Thanks in advance for your help!
Comments
-
I figured it out on my own. Post solution in case others have the same issue.
I was over complicating the Beast Modes:
Project Hit Rate:
CASE
WHEN IFNULL(COUNT(`Quote Number`),0)=0
THEN 0
ELSE
Sum(case when `Converted to Order?`='Yes' then 1 else 0 end)
/
COUNT(DISTINCT `Project Name`)
ENDQuote Hit Rate:
CASE
WHEN IFNULL(COUNT(`Quote Number`),0)=0
THEN 0
ELSE
Sum(case when `Converted to Order?`='Yes' then 1 else 0 end)
/
COUNT(`Quote Number`)
END2
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive