3

My data set has a {Invoice No} field that I want to sum the cost of. All invoice numbers are unique and have one cost value. However, because there are multiple {Part No} fields in each {Invoice No}, Quicksight multiplies this one cost by however many part numbers there are in each invoice, so the sum ends up being some multiple of the real value that I want. This is what the table view looks like:

{Invoice No}    {Part No}     {cost}
12345            001           10.12
12345            002           10.12
12345            003           10.12

How can I just use the first {cost} value of each {Invoice No}, so it doesn't duplicate it? I thought a workaround could be creating a new field that omits any duplicate invoice numbers, but don't know how to do that in Quicksight. Any solution would be much appreciated.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Big Chungus
  • 185
  • 1
  • 3
  • 13
  • @John Doe is your end goal to show a table visual or something else? – mjgpy3 Jul 25 '19 at 12:53
  • @mjgpy3 it would be nice to get it to show correct values for a start, and then maybe I would do a sum of all the unique costs over a time period, but that's not the problem I'm concerned about. – Big Chungus Jul 26 '19 at 14:06

2 Answers2

5

There is another type of calculation called a Level Aware Aggregation. The syntax is very similar to a Table Calculation (you use the same functions), but you dont need to use the 'group by' fields in your visual. The result is not an aggregate value - it is alike a subquery that gets joined back to your data at a row level. One way to de-dupe the data at the Invoice ID level is to take the max PartNo per Invoice and return the cost for those rows by not the others. It could look something like this:

ifelse( maxOver(PartNo, [Invoice No] , PRE_AGG) = PartNo, cost, null)

Then drag that into your visual and sum it.

The lack of aggregation inside the maxOver along with the addition of PRE_AGG on the end turns it into a Level Aware Aggregation.

Can check out this blog post for more on LAAs:

dondata
  • 81
  • 1
  • 2
  • 1
    This almost works for me, except maxOver doesn't seem to have a tiebreaker. In this example if partNo is duplicated, along with InvoiceNo, you would get duplicate costs in there. – Msencenb Nov 17 '21 at 20:38
  • Nevermind! I was able to manufacture a tiebreaker by having my 'PartNo' joined against a unique integer in the left join (the column causing the dups in the first place), which worked. – Msencenb Nov 17 '21 at 20:53
1

I can get this to work in a table by using a calculated field.

The calculation I am using is

sumOver(max(cost),[{Invoice No}])

Which basically states, grouped by {Invoice No} take a max of the values in the cost field (max is arbitrary since they're all the same value). Then sum the results.

Unfortunately this didn't seem to work in a KPI card (probably nicest bet for just showing a sum). It yielded the error Table calculation attribute reference(s) are missing in field wells.

To test this, I expanded your data set a bit

Invoice No,Part No,cost
12345,001,10.12
12345,002,10.12
12345,003,10.12
12346,001,42.42
12346,003,42.42
12347,0032,3.01
12348,0033,.04

Then displayed the value in a table, including sum Field wells Invoice cost table

mjgpy3
  • 8,597
  • 5
  • 30
  • 51