5 Replies Latest reply on Sep 17, 2018 7:29 AM by Matt Schutz

    Find users in Period 13 not found in Period 1-12

    Matt Schutz

      I have data with a date and a user ID.  I want to get the number of users who logged in during month X but did not log in during the previous 12 months.  I feel like this is an LOD expression but can't wrap my brain around it.  Any help appreciated.

        • 1. Re: Find users in Period 13 not found in Period 1-12
          Deepak Rai

          Do you have some fake data set?

          • 2. Re: Find users in Period 13 not found in Period 1-12
            Matt Schutz

            Let's say today is 6/1/18. I want to find the number of members logged in during June who did not log in during the previous 3 months. This would return Doug, who has never logged in at all before 6/1, and Cathy, because she hadn't logged in since 1/1.

             

            I have attached some sample data in a TWBX.

            • 3. Re: Find users in Period 13 not found in Period 1-12
              swaroop.gantela

              Matt,

               

              Here's one attempt. It uses DATETRUNC and LODs

               

              1. Logged in This Month:

              IF DATETRUNC('month',[Login Date])=DATETRUNC('month',[Today])

              THEN 1 ELSE 0 END

               

              2. Logged in any of previous 3 months:

              IF DATETRUNC('month',[Login Date])=DATETRUNC('month',DATEADD('month',-1,[Today]))

              OR DATETRUNC('month',[Login Date])=DATETRUNC('month',DATEADD('month',-2,[Today]))

              OR DATETRUNC('month',[Login Date])=DATETRUNC('month',DATEADD('month',-3,[Today]))

              THEN 1 ELSE 0 END

               

              3. Flag Logged in this Month:

              { FIXED [User]:MAX([1. Logged In This Month])}

               

              4. Flag User Logged in any of Previous 3 months:

              { FIXED [User]:MAX([2. Logged In 3 Prev Months])}

               

              5. Select Users logged in this month but not prev 3:

              IF [3. Flag User Logged In This Month]=1 AND

              [4. Flag User Logged In Prev 3Months]=0

              THEN "Logged in This Month not Prev 3"

              END

               

              Please see workbook v10.5 attached in the Forum Thread.

               

              281990flag.png

              • 4. Re: Find users in Period 13 not found in Period 1-12
                Deepak Rai

                Here it is ;

                You need This Calculation , I call it Login Times and Made it as RUNNING Total

                 

                 

                 

                LOGIN Times

                 

                IF DATE(DATETRUNC('month', [Login Date]))>=DATE(Dateadd('month',-3,{MAX(DATETRUNC('month', [Login Date]))})) and

                DATE(DATETRUNC('month', [Login Date]))<=DATE({MAX(DATETRUNC('month', [Login Date]))})

                THEN [Number of Records] END

                 

                and then Use This Filter as TRUE to get above view.

                 

                Thanks

                Deepak

                If it Helps, Pl mark it Helpful and CORRECT to Close Thread

                • 5. Re: Find users in Period 13 not found in Period 1-12
                  Matt Schutz

                  Thank you Deepak Rai.  That definitely helps.  What I need to get to ultimately is the count by month (not a list of users).  For example, June would be just the number 2.  I know that I simplified the example with some very basic sample data.  I tried modifying the calculation and filters to get what I'm looking for, but I think I just broke everything when I did.  Also, I haven't seen a LOD without a dimension before like { MAX(SomeMeasure) }.  I've only used { FIXED Dimension : MAX(SomeMeasure) }.  Can you explain that, and how that might affect what I'm trying to do here?  If you could attach the TWBX that you created that would be great.

                   

                  Thank you so much!