2 Replies Latest reply on Dec 13, 2015 3:49 AM by trevor.yip

    Cumulative Count Distinct

    trevor.yip
    trevor.yip Dec 11, 2015 3:42 PM

      Hi all,

       

      I'm a bit newer to Tableau, so I'm hoping for some help from the community.

       

      Situation:

      I want to count distinct locations ordering on a weekly basis, with a running total.

      Below are the #clinics ordering on a weekly basis.

       

       

      I am using CountD for each week; however, when I apply a running total, it's possible to count the store twice. (See Sheet 2 for additional details)

       

      I would like to see the numbers accumulating appropriately from 19->60->86->etc...->337

      Currently on sheet 1, it's 538, which is incorrect.

       

      Goal:

      Is there a way to count distinct locations to that week, excluding previous weeks?

       

      As an additional note, I'm one of the few users at my company using Tableau and trying to get this fixed rather quickly so that I can effectively "sell" the company on using "Tableau."

       

      Thank you all in advance.

        • 1. Re: Cumulative Count Distinct
          kumar.c
          kumar.c Dec 11, 2015 10:48 PM (in response to trevor.yip)

          Hi Trevor,

           

          I made changes in the sheet2, is that what you are looking for.

           

          Please find the attached workbook.

          • 2. Re: Cumulative Count Distinct
            trevor.yip
            trevor.yip Dec 13, 2015 3:49 AM (in response to kumar.c)

            Hi Kumar,

             

            My apologies... I'm actually hoping to update Sheet 1 (the line graph over time). I realize now that my original post does not have the right numbers, as you'll see below. What I'm trying to do is calculate the total number of distinct locations ordering to date (for that week).

             

            i.e...

            For week 1, we see 19 distinct locations ordering.

            For week 2, we see 56 distinct locations ordering (this includes the 4 locations re-ordering.)

            For week 3, we see 78 distinct locations ordering (this includes the 5 locations re-ordering)

            etc...

             

            I believe the reason is because for those locations that are re-ordering, they get counted as 2 (and higher for more recent weeks), which I'm guessing is why the count gets messed up when trying to do a running total.