How to create simple tables with both variables created in beast mode, using window functions
Hi,
I am trying to partion by a beast mode and then creating a table based to provide an overview of how many agents fall into a certain quadrant.
1. I created a beast mode that places each customer into the scatterplot based on the two score I defined:
advisory score= sum(case when product='nx' then 'score' else 0) / sum(case when product='nx' then 1 else 0)
sales ratio=sum(sales)/sum(attempts)
2. I created another beast mode variable that determines which quadrant they fall in:
case when (
sum(case when product='nx' then 'score' else 0) / sum(case when product='nx' then 1 else 0))>0.5 and
(sum(sales)/nullif(sum(attempts))),0)) >0.4
then 'Q1' else
(case when (
sum(case when product='nx' then 'score' else 0) / sum(case when product='nx' then 1 else 0))>0.5 and
(sum(sales)/nullif(sum(attempts))),0)) <=0.4
then 'Q2' else
(case when (
sum(case when product='nx' then 'score' else 0) / sum(case when product='nx' then 1 else 0))<=0.5 and
(sum(sales)/nullif(sum(attempts))),0)) <=0.4
then 'Q3' else
(case when (
sum(case when product='nx' then 'score' else 0) / sum(case when product='nx' then 1 else 0))<=0.5 and
(sum(sales)/nullif(sum(attempts))),0)) >0.4
then 'Q4'
end) end) end) end
3. I am trying to get a simple table like in the attachement using the HTML Table.
But when I drag the variable I created in 2 into columns and also drag count of agents it all just collapses to one row (attached in 2nd pic) and counts the row entries.
Any idea how to solve that??
Any help is much appreciated!
Comments
-
Hi @user084060
I'd recommend simplifying your beast mode so you don't have so many nested case statements. You can have multiple WHEN statements in your when clause. You don't need to create a new case statement in each else clause.
Try something like this:
case when (sum(case when product='nx' then `score` else 0 end) / sum(case when `product`='nx' then 1 else 0 end))>0.5 and (sum(`sales`)/nullif(sum(`attempts`))),0)) >0.4 then 'Q1' when (sum(case when product='nx' then `score` else 0 end) / sum(case when `product`='nx' then 1 else 0 end))>0.5 and (sum(`sales`)/nullif(sum(`attempts`))),0)) <=0.4 then 'Q2' when (sum(case when product='nx' then `score` else 0 end) / sum(case when `product`='nx' then 1 else 0 end))<=0.5 and (sum(`sales`)/nullif(sum(`attempts`))),0)) <=0.4 then 'Q3' when (sum(case when product='nx' then `score` else 0 end) / sum(case when `product`='nx' then 1 else 0 end))<=0.5 and (sum(`sales`)/nullif(sum(`attempts`))),0)) >0.4 then 'Q4' end
It's combining it into a single row because your beast mode is only returning Q1. If the updated beast mode continues to return only Q1 I'd recommend you create beast modes (if you haven't already) with the formulas for your advisory score and sales ratios, toss them onto your table and see if you get other values besides ones that would cause them to fall into the Q1 bucket.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I recoded my Quadrant formula as you suggested, but unfortunately it is still collapsing to Q1 and the row count when just create a view that shows how many agents fall into each quadrant.
I attached the table and as you can see all of my variables are created in beast mode. My question now is, is it still possible to create the view I described with my current set up?
0 -
-
What you're asking for is not possible without pre-aggregating your data in ETL or a dataset view.
What you described is wanting your ratio calculated at one row per customer SUM(...) / SUM(...)
To do that you had to put Customer on the Axis.
But you don't want one row per customer you want groups of the ratio (hence the CASE statement).
To get rid of each customer ,you took it off the axis. But then the ratio, SUM(...) / SUM(...) , recalculates at the granularity of the card which is ... All customers.
This is why you're getting just one row.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive