Sum while only taking distinct IDs into account

Q&A
, , , ,
1

We have mutliple Orders, each with multiple ArticleIDs of which each Article has a Time.

{7E4DD514-D258-44CF-B908-918D7D372C79}
{7E4DD514-D258-44CF-B908-918D7D372C79}299×376 2.99 KB

We want to create the sum of Times for each Order and display the average over all Orders (per day). For the example table this would be:

Sum of 1: 21

Sum of 2: 21

Sum of 3: 24

Average of sums: (21+21+24)/3=22 →an order on this day takes 22 hours on average

Currently this is done with sum({Time}, [{OrderID}]) and selecting “average” as the aggregation in the visual. The problem now is that we have duplicates of IDs which we cannot get rid of as we need separate rows because of the “forFilter” column, in the example this is the case for ArticleID=H. The duplicated Times are also taken into account with the current solution, meaning 4 is counted 3 times instead of once and the sum of Order 3 will be 32 instead of 24. There is another calculated field “Times distinct”: min({Time}, [{ArticleID}] which aggregates the runtime per ID, but this cannot be used in the other sum as “Visual agg functions can’t be used operands of as Level Aware Calculation (LAC) agg functions”. We also cannot move the calculation to sql, because we want to later filter values based on the “forFilter” column in the analysis. Those values then also should be excluded in the calculation (as the data lies in SPICE, dataset parameters are not an option).

Therefore my question is: is there some way to sum over values while only counting each runtime of distinct IDs?

2

Hi @Caro,

I think I understand your issue. You’re basically trying to nest 3 aggregations - min grouped by forFilter and OrderID (LAC-A), followed by sum grouped by OrderID (LAC-A), followed by avg.

QuickSight doesn’t allow you to nest LAC-A functions but you can use LAC-W inside of LAC-A, so you can use minOver instead of min. Dividing by countOver essentially cancels out your duplicates (4/3 + 4/3 + 4/3 = 4).

avg(
    sum(
        minOver(Time, [Order, forFilter], PRE_AGG)/countOver(ArticleID, [OrderID], PRE_AGG), 
        [OrderID]
    )
)