CODEWITHSUNDEEP
tableau-desktop
varun agnihotri
varun agnihotri

Reputation: 1

Create a custom calculation in tableau. Count on the basis of a condition

I have a lead lifecycle where I have to count the number of marketing qualified leads that have the MQL state in a selected duration BUT they shouldn't be in the MQL stage any time before that time (i.e before the start time of the selected date).

For example in the screenshot that I have attached I can see that the number of MQLs between 1st Nov 2022 & 30th September. I don't want to see the leads in this count that have the MQL status before 1st Nov 2022.

enter image description here

Upvotes: 0

Views: 80

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11921

In the general case, the expression INT() returns 1 or 0 for each record to indicate whether the is satisfied for that row -- i.e. INT() converts TRUE to 1 and FALSE to 0.

So you can either

  1. use filters to enforce your boolean condition, reducing to only the records of interest and then use SUM(), COUNT() or COUNTD() to calculate your aggregate value, depending on your goal. OR
  2. aside from your filters, reflect your boolean condition of interest inside a calculated field, and then again use an aggregation function like SUM() or COUNTD() to calculate the aggregate value you want.

The second approach is useful when you want to retain records in your analysis that don't satisfy your boolean condition - e.g., if those other records are needed for other calculations.

As an example, if you define a calculate field called Num_Leads_In_Interval as INT([Qualified Lead] AND [Date] >= [Interval_Start_Date] and [Date] <= [Interval_End_Date])

Then simply use SUM([Num_Leads_In_Interval]) in your view and change the parameters controlling the Interval start and end date as desired.

Again, the simplest approach is to define your filters on the filter shelf instead of in your calculations, but the filter shelf applies to the entire view, so if you want to be more selective about it for individual measures, you can essentially move your filtering criteria from the (more global) filter shelf into the (more specific) calculated field(s).

Upvotes: 0

Related Questions