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

    exclude duplicates from previous week

    Shri Siva

      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

        

      83126146164169
      PART_NUMBER    W37W38W39W40W41
      806-0714-25+11111
      806-0724-21+11111
      87-1339C#33300011
      87-1339U+33300011
      87-1390U+33211111
      87-1391U+33201111
      87-1804Z+01000111
      87-1818R+01000011
      87-18B20+T3E11111
      87-18B20+U3E01111
        • 1. Re: exclude duplicates from previous week
          Ivan Young

          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
            Shri Siva

            thanks a lot. it worked exactly as i wanted