To generate beast mode calculated field.

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.

Answers

  • sdethe
    sdethe Domo Employee

    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
    Lester_Tai Member

    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.

    image.png
    image.png

    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

Categories