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

Running Count Of Distinct Applications Per Month

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

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.