5 Replies Latest reply on Jan 31, 2018 9:25 AM by Jim Dehner

    Correct count for active users

    John Powell

      Hi all


      Really hoping someone can help, I'm well past my level of knowledge within tableau on this one!


      I have a workbook based on a SQL query.  It is designed to get per head data based on several metrics.  For example the number of new jobs added per head.


      I can't use the status of the staff record because that would mean historic head counts are wrong.  Instead I've counted the number of each metric per user within the SQL and then stated that if the count of any of those is greater than 0 then they must be active (the column "Active this month" is populated with yes or no values).  The date of these metrics is then cast to year, month and day.


      The counts for each metric are coming out correctly but I'm struggling to get an accurate count of the active users in each week.  It's coming out as way more than I know to be true so when I take a count of jobs added and divide that by the number of active users I'm generating for headcount the end result is coming out far too low.


      I've calculated headcount based on:


      IF [Active This Month] = 'yes' THEN 1 ELSEIF [Active This Month] = 'no' THEN 0 END


      I think I can see where it's going wrong, I think the count is wrong because it's counting too many 1s because I've cast that out to each individual day but I'm not entirely sure.


      Any help is greatly appreciated!