To generate beast mode calculated field.

I need to get percent complete in Gantt chart per below request.
Filter column `Region_TWC` to get each filtering. ‘NAM’ , ‘EUROPE’ , ‘PACIFIC’ , ‘JAPAN’ , ‘KOREA’ , ‘GCA’ , ‘SEA’.
Then filter column `Meet FOB DL_AR`, to get the rows which is equal to 'Meet AR DL'
After above filtering, to get each region’s total `Ordered unit` in column `Ordered unit`.
Comparing each region’s total `Ordered unit’ to SUM(distinct(`PAR in PQ`)) in column `PAR in PQ`
If total `Ordered unit` is equal or more than SUM(distinct(`PAR in PQ`)), return SUM(distinct(`PAR in PQ`)) else return total `Ordered unit`.
Use the return number to be divided by SUM(distinct(`PAR in PQ`)) to get percent complete in Gantt chart.
Answers
-
Hello @Lester_Tai ,
Thanks for reaching out to our community forum.
Beast Mode (Percent Complete per Region)
CASE
WHEN SUM(`Ordered unit`) >= SUM(DISTINCT `PAR in PQ`) THEN
SUM(DISTINCT `PAR in PQ`) / SUM(DISTINCT `PAR in PQ`)
ELSE
SUM(`Ordered unit`) / SUM(DISTINCT `PAR in PQ`)
END
>>> Apply the filters in the card or ETL:Region_TWC IN (‘NAM’, ‘EUROPE’, ‘PACIFIC’, ‘JAPAN’, ‘KOREA’, ‘GCA’, ‘SEA’)
Meet FOB DL_AR = ‘Meet AR DL’
Group the chart by region to get results per region.
Could you please try the above solution see if it resolves the issue. If you still encounter an issue. I would suggest opening a support ticket. Our team will be happy to assist you further. When submitting the case, please include relevant details such as URL of dataset/account, screenshots of error. This information will help us understand the issue better and provide a timely solution.0 -
Sorry, i it doesn't work. I changed the beast mode to as below.
CASE
WHEN SUM(CASE WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')
AND `PO Type` IN ('Sample','Demo','SF','M0','M1') AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit` END)
>= SUM(DISTINCT `PAR in PQ`)
THEN SUM(DISTINCT `PAR in PQ`)
WHEN SUM(CASE WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')
AND `PO Type` IN ('Sample','Demo','SF','M0','M1') AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit` END)
< SUM(DISTINCT `PAR in PQ`)
THEN SUM(CASE
WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')
AND `PO Type` IN ('Sample','Demo','SF','M0','M1')
AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit`END)
END
However, when I generate Sum of each region subtotal number, I found it is not aligned to total number. Take one project as an example as below.
Total number 38k vs. 34k if just sum of each region’s number.
I think the reason is because some regions ordered qty is more than theirs "PAR in PQ" while the rest regions ordered qty is less than "PAR in PQ". When we capture all regions total qty, it is not calculated each region's data to get sum but check total qty against total qty in "PAR in PQ" to cause the issue.
How could I resolve it?
Thanks
0 -
Hi,
Let me explain it more clear.
Below is the example:
- I have a PO list in the table and would like to get each region's data per my beast mode.
- Per my request, if "Ordered unit" is equal or more than "PAR in PQ" and "Meet AR DL", return "PAR in PQ" else return "Ordered unit".
- When I run the best mode, it will be correct when filtering each region. However, if I don't do filter but only want to see the sum of all regions, it will be wrong because the mode will only comparing total number in "Ordered unit" and "PAR in PQ".
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive