Jatin Kakani — Sadly this is one of those scenarios for which Tableau's basic ETL will not carry you. It requires external ETL, such as the SQL window functions that you used to solve for this.
Here is the conundrum you face in this scenario:
- You cannot use an LOD expression to collect rows based on an inequality. In other words, you cannot use LOD expressions to answer the question, "Given a value X in the current row, find all the rows that have a value [greater than | less than | anything other than equal to] the value X". LOD expressions can only be used to collect rows that have the same value. Let that concept marinade. It is the number one trap that you will repeatedly fall into as you learn LOD expressions. I know I did.
- Consequently, your only other option is to try to use table calculations to arrive at "Prior Engagement". That in itself will introduce complexities to your view construction, but in many scenarios it's doable — though often cumbersome and even non-performant, depending on data set size. Unfortunately, this approach will result in your "Prior Engagements" calculation being a table calc, and that brings us to our third conundrum:
- Table calculations cannot be used to govern the window size (partition) for another table calculation. The approach in #2 will require you to (1) model Prior Engagements at the individual lead level, (2) sum them up, then (3) use the "first row filter" trick to make it appear that Lead ID is not a level of detail in your view. Unfortunately, (2) and (3) require table calculations that are performed over a window that is segmented by Prior Engagements. But since we had to compute Prior Engagements as a table calculation, it is not available to partition the window needed for (2) and (3). At this point, the solution is untenable.
So, my verdict is NOT DOABLE. Each time I've encountered this pattern, I've always had to solve for it using SQL window functions. That said, I would love to hear from the masters. If there is a trick that I've overlooked, I would love to learn it. In my work environment, minimizing reliance on custom SQL is ideal.
If you would like to see how far we're able to get, please see the attached workbook. Here are the steps I followed in constructing the view up to the point where the solution hits a wall.
STEP 1. Create a calculated field [Call made in month for lead]. This is to account for the fact that you seem to have multiple "tasks" for a lead, but your requirements suggest that prior engagement is a function of the lead (not the task) and also of the month. (In other words, it doesn't matter how many calls are made for the lead in a month; you just need to know that at least one call was made for that lead in a given month.)
[Call made in month for lead]
{ EXCLUDE [Task Id]: MAX(1) }
STEP 2. Create a calculated field [Prior months called]. This is your first table calculation. On a view segmented by lead and by month, it will identify how many prior months a call (engagement) was made for that lead. Its construction is designed to ensure that it does not evaluate to a number for a month in which no call was made on the lead. Configure the table calculation to compute along [Task Created Date].
[Prior months called]
ZN(WINDOW_SUM(SUM([Call made in month for lead]),FIRST(),-1))
* ATTR([Call made in month for lead])
STEP 3. In the field list, Change the [Prior months called] field to Discrete. The pill should change from green to blue.
STEP 4. Create an initial view: MONTH([Task Created Date]) on the Columns shelf, [Lead Id] and [Prior months called] on the Rows shelf, and ATTR([Call made in month for lead]) on the Marks shelf as a Text label. Using this view, you can see that Prior Engagements is properly calculated for each individual lead, as calls are made in subsequent months.
You can reverse [Lead Id] and [Prior months called] on the Rows shelf, and now you're starting to get close to what you wanted to achieve.
STEP 4. Create a calculated field [Total calls made]. This is the calculation that should total up the leads with 0 engagements, 1 engagement, etc. for each month. This would be the first step toward getting a single row per engagement bucket (in conjunction with the "first row filter" trick. Unfortunately, due to the limitations described at the beginning of this article, you won't be able to configure this calculation the way you need to. We will create it just to illustrate the conundrum. Configure the table calculation to compute along [Lead Id].
[Total calls made]
WINDOW_SUM(SUM([Call made in month for lead]))
STEP 5. Observe the conundrum. Under "Compute using" (in the above dialog box), select "Advanced…". From this window, you will see where we run into a problem: [Prior months called] is not included in the Partitioning list, because it is a table calculation and not a dimension. Without [Prior months called], we won't be able to calculate over the correct window size.
STEP 6. Add [Total calls made] to the Marks shelf as a Tooltip. Look at the value and observe that it's not adding up the calls for each [Prior months called] bucket; it's adding up all of the numbers for each month.
FINAL NOTE
Besides SQL window functions, I'm not sure what other options you have. From my limited experience with Tableau Prep Builder, I believe it is not capable of solving this scenario. It has limited/no counterpart to Tableau Desktop's table calculation functions, so it can't be used to solve the types of scenarios that SQL window functions are designed for. I never had occasion to test Alteryx in this scenario, so I'm not sure if it offers better tools for modeling this type of exercise.
I'm sorry I am not able to show you a solution, but I hope this helps give you a better understanding of the finer points of LOD expressions and table calculations, and the potential caveats to watch out for when using each feature.
Workbook attached (version 2019.2)
