10 Replies Latest reply on Feb 14, 2018 3:16 PM by Justin Larson

# Calculation: Sum of distinct records

Hello Team,

I attached the source file. Requirement is to get the unique records and then sum. Please see the below screenshot for more details.

I also attached the .twbx

• ###### 1. Re: Calculation: Sum of distinct records

Hi Srikar,

Try with below calculation...

Regards

Aman

• ###### 2. Re: Calculation: Sum of distinct records

Good morning

here is at least one solution

copy the QTY and convert it to a dimension

next create this calculation for max

then make the viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Calculation: Sum of distinct records

Hi, Srikar

Why don't you use column1 as filter shown below to filter the duplication?

ZZ

• ###### 4. Re: Calculation: Sum of distinct records

Hi Aman,

Thanks for the solution. But it works when the same Qty repeats across multiple records.

In my case there are few instances where I see different QTY as shown below. In this case your logic doesn't work.

• ###### 5. Re: Calculation: Sum of distinct records

Hi Jim,

As per my data, the maximum logic doesn't work in all scenarios...please look at one of such instance below.

• ###### 6. Re: Calculation: Sum of distinct records

Hi ZZ,

The data is dynamic in column 1 so I cannot hard code the filter.

• ###### 7. Re: Calculation: Sum of distinct records

Hi, Srikar

Now is much clear what you need. please try below calculation

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Calculation: Sum of distinct records

see below and attached

seems to work just fine

please show your TWBX workbook instead of excel so we can see what is actually going on

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 9. Re: Calculation: Sum of distinct records

Hi,

This is what you probably need.

and this

• ###### 10. Re: Calculation: Sum of distinct records

What is the utility of the duplicate records? It may be best to simply filter them out of the dataset. If this is a pragmatic statement, there are a few ways to approach that, depending on data source.

If you truly must keep all the records, the only reliable way to identify a unique record (outside of having a repeating primary key) is to include all of the fields. So the LOD calc would look like

{FIXED [all of your column names, comma delimited]:MIN([Qty])}

the MIN could really be min, max or average, because by definition the value is the same for every row that is being considered.

Then drop that in the view as a sum to get the aggregate you're after.