Adjustable % column

kboudrie

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?

MarkSnodgrass

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.

MarkSnodgrass

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.

kboudrie

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

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.

kboudrie

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