I'm not clear on your exact data structure. Assuming that each usage session is generating an additional row in the data source then the usual method would be to do an aggregation step with a Group By on the user Z getting the Min of usage session Y. Depending on how your data is set up you might be able to use the aggregated results as-is or might have to join them back to the original non-aggregated data. In the latter case the way we can do that in Prep is to add a branch step to the data and then join the aggregate results to the branch.
You are a genius!! I've been trying so many complicated approaches to amend the formula while the solution was that simple
One more question: any idea on how to calculate the time difference between the two dates of service usage of the same user if there are more than 2 values?
For example, if I have a user:
Z (ID) - "Pink Unicorn"
Y (usage_session) - 12/01/2018
Y (usage_session) - 17/04/2019
Y (usage_session) - 24/04/2019
Then I would like to have a difference between the first two Ys and then another difference between the 2nd and the 3rd Y All the dates are in the same column. Hence, the formula should only take two consecutive values from that column
This ask changes things because the goal is to do an inter-record comparison and that isn't well-supported by Tableau Prep. There are two methods for this:
1) Set up a self join using a condition where date < date and then filter out all the extra rows. This gets you a data set with the next dates as additional columns and then we can do a DATEDIFF() between them. I built this in the attached .TFL file. The one challenge with this is that if there's some condition where you could have multiple entries on the same day you have to be careful of the joins.
2) Generate a row number that identifies each row in the sequence for each user/customer, then do a self-join on the row number = row number - 1. This gets around the multiple entries on the same day problem because you've explicitly identified the sequence. Tableau Prep doesn't yet support row numbering tho users have come up with workarounds.
Ok. Super thanks for your hyper-qualified help I will find a way to calculate this in our own code