To generate beast mode calculated field.

Lester_Tai

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.

sdethe

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.

Lester_Tai

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

Lester_Tai

Hi,

Domo post example.xlsx

Let me explain it more clear.

Below is the example:

1. I have a PO list in the table and would like to get each region's data per my beast mode.
2. 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".
3. 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".

Domo post example.xlsx