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.
I created below calculated field to get each region subtotal number.
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. Total number 38k vs. 34k if just sum of each region’s number.
