Adjustable % column

kboudrie
kboudrie Member
edited February 2021 in Charting

I'm working on an ask that I'm not sure is possible to meet. I have delivery data that I need to calculate the on time percentage over an adjustable timeframe. My key columns are supplier name (column1), PO lines delivered (column2), and PO lines delivered on-time (column3). I also need to display a column that show the percentage of lines that were delivered on-time (column3/column2). The difficulty I'm running into is that the on-time percentage has to be calculated at a fixed timeframe, so regardless of whether I've built it as a beast mode or in ETL, it's "set" at a week or month or however I aggregated the number of lines. My boss wants the on-time % to adjust and be accurate whether we've quick filtered to a week or a month or a year of data. The second image below shows that this causes duplicate lines for each company since I can aggregate the total lines and the lines on time but not the % on-time.

I've been thinking through this a lot and I believe the problem comes down to the order of operations. The % on-time calculation has to be done at whatever timeframe I set within the beast mode or in ETL prior to aggregation. What I'm being asked to do is perform a division calculation AFTER aggregation.

Does anyone know of a way to do this or a workaround?

image.png
image.png


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Ahh, I see that now in the sample data I mocked up. When I changed the beast mode from

    (`Lines on Time`) / (`Number of Lines`)

    to

    SUM(`Lines on Time`) / SUM(`Number of Lines`)

    then the average aggregation works properly.

    Give that a try and see how it works for you.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    On your columns, set the aggregation to Sum for your Number of Lines and your Lines on Time columns. On the % on time, set that aggregation to Average. This should allow Domo to auto-calculate for you no matter how many weeks are selected and just show one company per row.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • kboudrie
    kboudrie Member

    Thank you for the suggestion. Unfortunately, the math doesn't work out for this.

    Example: Company A received 5 lines during week 1 and all 5 lines were on-time. The on-time % for that week is 100%. Week 2, they received 10 lines and all 10 were late. The on-time % for that week is 0%. Looking at weeks 1 & 2 together and using the sum aggregation on number of lines & lines on-time but the avg aggregation on on-time %, it would read 15 lines received, 5 on-time, 50% on-time when it should read 33% on-time.

    I appreciate the suggestion and it gets it close but not close enough.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Ahh, I see that now in the sample data I mocked up. When I changed the beast mode from

    (`Lines on Time`) / (`Number of Lines`)

    to

    SUM(`Lines on Time`) / SUM(`Number of Lines`)

    then the average aggregation works properly.

    Give that a try and see how it works for you.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • kboudrie
    kboudrie Member

    Aggregating within the beast mode did the trick. Thank you so much!

Categories