8 Replies Latest reply on Jan 23, 2019 10:18 AM by andrew.schon

# Sum De-Duped Rows

All,

I've been trying to understand how FIXED works but I'm running into some issues. I have a dataset where I need to sum all records, but de-duplicate based on exact dates (including specific time) and interaction type. So if two records have the same interaction type and same date, de-duplicate them.

Example:

So in reality, the sum should be 2 after de-duping two records at 1/9/19 12:36 and two records at 1/9/19 11:50 (both expert conference call).

thanks

Andrew

• ###### 1. Re: Sum De-Duped Rows

You should be able to use a fixed LOD as you theorized.

{FIXED [Type],[Date]: MAX(1)}

The MAX(1) will just give us a 1 for each unique pair of Type & Date. We can't just put 1 because LODs require that the measure be aggregated.

See attached sample.

1 of 1 people found this helpful
• ###### 2. Re: Sum De-Duped Rows

Also, if you need some assistance with understanding fixed LODs, here are a couple of good resources:

• ###### 3. Re: Sum De-Duped Rows

Thank you! This makes way more sense in practice now - appreciate the help!

• ###### 4. Re: Sum De-Duped Rows

Any time!

• ###### 5. Re: Sum De-Duped Rows

One quick follow up, Ken:

I ran a quick table calc based on the fixed LOD:  TOTAL(SUM([Interactions De-Duped])) because I want to show the total in the title, as well. but the number it's giving me isn't the sum of all the records on the page see bottom left sum vs top sum in title. Why the discrepancy and what's the best way to show the number on the bottom left instead?

thanks again

• ###### 6. Re: Sum De-Duped Rows

Any chance you can share the workbook?

• ###### 7. Re: Sum De-Duped Rows

The issue is that the LOD is only looking at unique combinations of Type and Date when it counts, but you've added Interaction Owner to your view, so the calculations for each bar then accounts for that field as well. So, your data must have some type/date combination in one Interaction Owner that is the exact same type/date as another. When you place that in your title, it's ignoring the interaction owner, so it's de-duping those as well. Does that make sense?

So, if you want to account for the owner, you'll need to edit your LOD as follows:

{FIXED [Interaction Owners1], [Interaction Type1], [Date1] : MAX(1)}

Please also keep in mind the Tableau order of Operations as you may need to add your filters to context if you want them to apply before the LOD is calculated. Tableau's Order of Operations - Tableau

1 of 1 people found this helpful
• ###### 8. Re: Sum De-Duped Rows

ahhh, this makes sense. Thanks - I obviously have more learning to do on how LOD functions.