2 Replies Latest reply on Apr 4, 2018 3:30 PM by Shri Siva

# exclude duplicates from previous week

HI,

i have a requirement where i need to get running sum of distinct part_numbers for each week.

If a part_number occurred in Week 37  and week 38 it should be counted only as 1.

In the below graph week 37(first week)  has 83 distinct part numbers which is correct number .

But in week 38 I should get only 126 records but I am getting 164 because in week 38 tableau is  counting same part numbers present in week 37 and 38 as 2 but i want to count it has only 1

I need to count like below table

806-0714-25+  part number is present in Week 37,38,39,40 ,41 so in the graph i need to count as only 1 for all weeks but in tableau it double counts each week.

I tried many ways using LOD but I am not getting 126 for week 38 .please help me solve this issue.I have attached the packaged workbook in 10.2

 83 126 146 164 169 PART_NUMBER W37 W38 W39 W40 W41 806-0714-25+ 1 1 1 1 1 806-0724-21+ 1 1 1 1 1 87-1339C#333 0 0 0 1 1 87-1339U+333 0 0 0 1 1 87-1390U+332 1 1 1 1 1 87-1391U+332 0 1 1 1 1 87-1804Z+010 0 0 1 1 1 87-1818R+010 0 0 0 1 1 87-18B20+T3E 1 1 1 1 1 87-18B20+U3E 0 1 1 1 1
• ###### 1. Re: exclude duplicates from previous week

Hi Shri,

You can do this with LOD, I can see you were on the right track with the min week field you created.  So what I did was create and additional field I called Distinct Part Number, the formula is below.  I also added the area filter to context, alternatively you can add area to the min week lod.  Let me know if you have any questions.

Regards,

Ivan

1.  Create Distinct Part Number:  IF [WEEK] = [min_week_part_number] THEN [PART_NUMBER] END

2. Add area filter to context

3. Create your running sum:  RUNNING_SUM(COUNTD([Distinct Part Number]))

2 of 2 people found this helpful
• ###### 2. Re: exclude duplicates from previous week

thanks a lot. it worked exactly as i wanted