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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 114 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4.1K Visualize
- 2.5K Charting
- 807 Beast Mode
- 80 App Studio
- 44 Variables
- 761 Automate
- 189 Apps
- 480 APIs & Domo Developer
- 75 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive