Hi, I'm trying to count distinct applicants by stage in the process but I'm running into issues where there are multiple dates for the same record (screenshot attached). I would like to be able to only count the most recent row and ignore historical.
I tried the following with no success
LOD expression: MaxDate = {Fixed : MAX([CreatedDate (Opportunity History)])}
Calculated field: MaxDate Count = COUNTD(IF MONTH([CreatedDate (Opportunity History)]) = MONTH([Calc MaxDate]) THEN [Opportunity ID] END)
Any suggestions on how I can count distinct applicants on time per date?
Thank you,
Matt
Hi Matt, an uploaded example workbook always helps, but I think I have an answer built off superstore data attached:
I did this using 4 calculated fields, there may be a more streamlined approach, but this works and is fairly efficient:
CountD: COUNTD([Order ID])
Max Order Date: {Fixed [Customer Name]: MAX([Order Date])}
CountDate T/F: If [Order Date]=[Max Order Date] Then True Else False END
CountDate: If ATTR([CountDate T/F]) Then [CountD] END
(This formula is correct, there is no =True, it performs much faster this way)
The CountDate Measure will only count orders on the maximum order date by customer:
A key for your formula, the Max Order Date LOD calculation will need all fields from your dashboard listed EXCEPT for the date. In your it looks like you have a code, win/loss, enrollment, admitted status, and department, so it would look roughly like: Max Order Date: {Fixed: Code, Win, Enrollment, Status, Department: MAX([Order Date])}
LOD's are complex, and their performance hits are even more complex, Tableau has a lot of great info to get you started published here: Overview: Level of Detail Expressions - Tableau
