1 Reply Latest reply on Feb 14, 2018 12:43 PM by Joe Oppelt

    Running Count Of Distinct Applications Per Month

    Cameron Powers

      I am new to tableau and trying to create some test report while on my trial (Version 10.5) but I am having an issue with distinct segmented calculations. I have a dataset with applications that have been identified and classified i.e. (application, date_captured, classification). This dataset contains duplicates of the same application that might of been captured at an earlier or later date. All I want to do is count how many distinct applications were captured in a particular month. The issue I am running into now is when I am segmenting, the new segment doesn't take into account applications that were captured in the previous month, and counts them a second time.

       

      When I try a `COUNTD(application)` The grand total value will be correct, but like I mentioned above the monthly values are being counted even if classified in the previous month.

       

      I have tried an LOD expression but don't fully understand how they work yet.

      test_earliest_app = {FIXED [Standardized Name]: MIN([Captured Date])}

      `{FIXED [application]: SUM(IIF([date_captured]=[test_earliest_app],1,0))}` 

       

      Is someone could tell me how they generally approach these types of distinct segmented problems, how you would solve this specific problem, and anything that might help me understand LOD's better I would really appreciate it.

        • 1. Re: Running Count Of Distinct Applications Per Month
          Joe Oppelt

          FIXED looks at all your data (whether or not you have filters on your sheet) and does what you tell it to do.

           

          {FIXED [Standardized Name]: MIN([Captured Date])}

           

          This will take all the rows for each [Standardized Name] and grab the min of all the [Capture Date]s, and on all rows for a given[Standardizwed name] the value in the calc will be that same min value.

           

          The best way to understand how something works is to create a test sheet and display the value.  So create that calc.  Put [Standardized Name] on ROWS.  Put [Capture Date] next on ROWS.  Put the new calc next on ROWS.  For each [Standardized Name] you will see a separate row doe each [Capture Date], and following each [Capture Date] you will see the earliest date of all the capture date on each row.

           

          And a FIXED LOD can be a dimension.

           

          Now you can do some other calc that will act only if the [Caprture Date] is equal to the Earliest Capture Date:

           

          COUNTD( IF [Capture Date] = [Earliest Capture Date] then [Standardized Name] END )

           

          (Yes, you can do IF logic inside a function!)

           

          Now you will count things only if the row is the earliest.