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

    Running Count Of Distinct Applications Per Month

    Cameron Powers
    Cameron Powers Feb 14, 2018 12:27 PM

      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
          Joe Oppelt Ambassador Feb 14, 2018 12:43 PM (in response to Cameron Powers)

          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.