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

    Comparing Data Across Months

    Mark Gordon



      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!




        • 1. Re: Comparing Data Across Months
          Mark Holtz

          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...