1 Reply Latest reply on Apr 18, 2018 6:36 AM by Mark Holtz

# Comparing Data Across Months

Hi

I think there is probably a really simple (and well documented!) solution out there but this has had me banging my head in frustration this morning....

What I am trying to do is to compare user logins month on month - I have a table which has the login date, company id and a product name and I want to see how many customers that logged into a certain product in a given month then logged in to the same product in the following month, and 12 months later, so I can work our recurrent usage patterns.  I've been looking at some examples of cohort analysis but can't seem to get one to fit. I've also tried to create a count of company id for each month (pretty easy) and then tried to use a LOD to bring back the subsequent number of those companies that were active 12 months on, but can't get this to work.

I've attached a mockup of the data I've been working with.  Any suggestions would be greatly appreciated!

Thanks

Mark

• ###### 1. Re: Comparing Data Across Months

I'm making some assumptions here based on my understanding what you're asking.

Are you specifically looking for ONLY users who logged in in the:

1) Given Month

2) Following Month

3) 12 Months Later

?

If so, maybe you could employ 3 calculated fields and then represent all 3 by user.

I'd create a parameter to specify the "month to analyze" (as a date input type)

The user will select the month to analyze.

Given Month calc:

IF DATETRUNC('month',[LoginDate]) = DATETRUNC('month',[Month to Analyze param] THEN 1 ELSE 0 END

Following Month calc:

IF DATETRUNC('month',[LoginDate]) = DATEADD('month',1,DATETRUNC('month',[Month to Analyze param]) THEN 1 ELSE 0 END

12 Months Later calc:

IF DATETRUNC('month',[LoginDate]) = DATEADD('month',12,DATETRUNC('month',[Month to Analyze param]) THEN 1 ELSE 0 END

Now you can represent all 3 measures simultaneously on a view.

I'm not exactly sure what you need to know to find patterns. You could filter any of them for zero, or you could create an additional calculated field as Field1 + Field2 + Field3 and filter that field = 3 (that guarantees user had activity in each period) or >= 2 so that you'd get activity in at least 2 of the periods...

This certainly could also NOT be the direction you're trying to go...