Get percent complete in Gantt chart

Lester_Tai
Lester_Tai Member
in Beast Mode

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.

image.png image.png
Tagged:

Answers

  • DavidChurchman
    DavidChurchman Coach

    To get an answer, I recommend you break up this question and ask just the first part. If you're still stuck after that, ask the next part. There's a lot going on and it's not clear what you're asking.

    At the end of your question, it seems like you're asking about why your sums aren't aligned 38K vs. 34K. I'm not sure how you're getting those sums, but I see a number of the BeastModes above using DISTINCT inside the SUM. This can often cause differences when breaking into groups vs. looking at it as a whole, as what is 'distinct' changes when broken up vs. looking at it as a whole. For example:

    Country

    Order Units

    Sum Distinct by Country

    Sum Distinct Total

    NAM

    1

    2

    16

    NAM

    1

    EUROPE

    1

    16

    EUROPE

    5

    EUROPE

    10

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Lester_Tai
    Lester_Tai Member

    David,

    sorry for confusion. 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".
    image.png
    Domo post example.xlsx

    Could you help check it again?

Categories