Creating advanced SUMIF() calculations in Quicksight

Question

I have a couple of joined Athena tables in Quicksight. The data looks something like this:

Ans_Count  | ID   | Alias

10        |  1     |  A
10        |  1     |  B
10        |  1     |  C
20        |  2     |  D
20        |  2     |  E
20        |  2     |  F 

I want to create a calculated field such that it sums the Ans_Count column based on distinct IDs only. i.e., in the example above the result should be 30.

How do I do that?? Thanks!

Answer 1

Are you looking for the sum before or after applying a filter? Sumif(Ans_Count,ID) may be what your looking for.

If you need to always return the result of the sum, regardless of the filter on the visual, look at the sumOver() function.

Answer 2

You can use distinctCountOver at PRE_AGG level to count unique number of values for a given partition. You could use that count to drive the sumIf condition as well.

Example : distinctCountOver(operand, [partition fields], PRE_AGG)

More details about what will be visual's group by specification and an example where there duplicate IDs will help give a specific solution.

It might even be as simple as minOver(Ans_Count, [ID], PRE_AGG) and using SUM aggregation on top of it in the visual.

Answer 3

If you want another column with the values repeated, use sumOver(Ans_Count, [ID], PRE_AGG). Or, if you want to aggregate via QuickSight, you would use sumOver(sum(Ans_Count), [ID]).

Answer 4

I agree with the above suggestions to use sumOver(sum(Ans_Count), [ID]).

I have yet to understand the use cases for pre_agg, so if anyone has concrete examples please share them!

Another suggestion would be to do a sumover + partition by in your table (if possible) before uploading the dataset, then checking if the results matche with Quicksight's aggregations. I find Quicksight can be tricky with calculated fields, aggregations, and nested ifs so I've been doing calculations in SQL where possible before bringing it in to quicksight to have a better grasp of what the outputs should look like. This obviously is an extra step, but can help in understanding how quicksight pulls off calcs and brings up figures (as the documentation doesn't always give much), and spotting things that don't look right (I've had a few) before you share your analysis with a wider group.