6 Replies Latest reply on Dec 21, 2016 9:56 AM by Madeleine Corneli

# Getting if user was present in previous month

Hey Guys,

We have a problem we want to solve using Tableau (10.1): we have a data source that has record for every time a customer used a product, something simple like this:

CustomerDatePresent in previous month (expected result)
A2016-07-01 13:51:000 (this is the first time A used the service)
A2016-08-05 14:12:001 (because of the previous record)
B2016-08-01 08:09:000 (first time B used the service)
B2016-10-03 07:04:000 (last time was 2 months ago, no record in September)
A2016-09-03 18:06:001 (there's a record for August)

and so on.. please note that the data is not ordered by customer.

Present in previous month: we want to tell if a customer used the service in the previous month or not. Not last / first usage or anything like that, just simply used the service. Can we solve this with a calculated field without joins / data blending? Normally I would blend this data source with itself on customer and month(date) = previous month, but I don't know if we can solve this without that, simply using a calculated field.

Thank you,

• ###### 1. Re: Getting if user was present in previous month

Hi Istvan,

Find my approach based on LOD-expression below as reference and stored in attached workbook version 9.3

a.First time used: {fixed [Customer]: min([Date])}

b.Last time used:  {fixed [Customer]: max([Date])}

c.Present in Previous Month:  if DATEDIFF('month',[Last time used],TODAY())=1 then "Used in Previous Month" else "Not used in Previous Month" END

• ###### 2. Re: Getting if user was present in previous month

Thank you!

• ###### 3. Re: Getting if user was present in previous month

Norbert Maijoor sorry if I wasn't clear: the calculation needs to tell for each record if the user used the product in the previous month. So using your example:

• A would be false for July, true for August (because used in July and that's the previous month in August), and false for November (because didn't use in October).
• B would be false - fasle, because did not use in August (for the September record), neither did she use it in October (month before November).
• C would be false-false-false

Does it make sense this way?

Thank you,

• ###### 4. Re: Getting if user was present in previous month

Istvan,

I believe that using a Table Calculation will allow you to compare a customer's usage dates to each other.  The first calculation compares a usage date to the previous usage date for that customer:

LOOKUP( ATTR(Date Used), -1)

* Edit Table Calculation and compute using Specific Dimensions (Customer, Date Used) restarting every Customer

The second calculation compares the previous value to the current Date Used value to determine if it occurred in the previous month:

IF DATEDIFF('month', attr([Date Used]), [previous value]) = -1 THEN 1 ELSE 0 END

* This calculation compares the first calculation (previous value) to the current Usage Date.  If the usage occurred in the previous month this calculation returns a 1 or else it returns a 0.

* Make sure that this calculation is set to be computed in the same way as the first one

Using this method will require you to have both Usage Date and Customer on the view so that the Table Calculations will compute correctly.

I've attached a workbook that demonstrates this solution.

Hope this helps!

• ###### 5. Re: Getting if user was present in previous month

Madeleine Corneli thank you, this is really close! However, this only works if a user has only one record for each month, doesn't it? So whenever a user uses a product let's say, 2 times in July and 3 times in August, we would get: false false, true, and false, false because of LOOKUP( ATTR(Date Used), -1), right?

Thank you,

• ###### 6. Re: Getting if user was present in previous month

The "within previous month" calculation in the attached workbook should account for this.  If you want to include records that occur in the previous OR current month then you can alter that calculation to be as follows:

if DATEDIFF('month',attr([Date Used]),[previous value]) <= -1 then 1 ELSE 0 END

If I understand correctly, the "<=" should fix the problem.