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)


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


      Distinct Users (the denominator)


      {EXCLUDE [Date] :


      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!