4 Replies Latest reply on Sep 25, 2017 7:42 AM by Bob Gale

    Need help with LODs for cohort analysis

    Bob Gale

      I'm having trouble using LODs to analyze how many users are participating in an activity vs. a total target audience. I've attached a v10.2 packaged workbook with a simplified version of the problem.

       

      The data structure consists of one table of users and demographic attributes outer joined with a table of events in which they may or may not have participated. So if a user has not participated in any events, their event columns will be Null.

       

      I'm trying to create a viz that can be sliced by different demographic cohorts and filterable by event time. So for a given period of time and for each cohort, I want a numerator bar showing the number of users who have participated in events superimposed over a denominator bar showing the total users in that cohort. So I created two calculations, and used and EXCLUDE LOD expression to remove event date from consideration in the denominator: 

       

      Distinct Users with Events (the numerator)

       

      COUNTD(
          IF ISNULL([ID (Events)])
          THEN NULL
          ELSE [ID]
          END
      )

       

      Distinct Users (the denominator)

       

      {EXCLUDE [Date] :
          COUNTD([ID])
      }

       

      This works until I drag [Date] to the Filters shelf. When I do that, users who have never participated in events disappear from the view!

       

      I tried using FIXED instead of EXCLUDE [Date], but then it ignores any demographic dimensions that I drag onto Rows. i.e. it shows 5 distinct users for both Male and Female instead of 3 Male and 2 Female. I want the denominator to slice of filter by any user attributes, but not event attributes.

       

      Thanks in advance for any help!

       

      Bob