6 Replies Latest reply on Feb 22, 2013 9:00 AM by Shawn Wallwork

    Including Zeros in Aggregated Distinct Counts and Grand Total Averages

    Pete Staley

      New Tableau user here. Running into a snag when trying to account for zeros in the aggregated averages of my text table. Zeros are defined as missing rows of data. So if I want last week's average, and the table does not have have data for Wednesday, it should still divide by seven.

       

      Objective:

      • Calculate distinct user counts (user_id) for each selected time interval (Parameter: Day, Week, Month, Year)
      • Calculate grand total row average (app) for each time interval
      • Calculate grand column average (session_date) for each time interval

       

      What's been done:

      • Setup date level calculation/filter/parameter that lets viewers see data by day,week, month, or year). This calculation truncates session date by the selected time interval.
      • Grand total row has been isolated by max(app value) != min(app value), and is proceeded by the appropriate average calculation.
      • Daily distinct user counts are calculated by concatenating user_id with app_id and session_date, then dividing the distinct user count by the distinct session_date
      • Attempted to isolate grand total column by using countd > # logic. The column counts distinct users and divides by ([End Date] - [Start Date]). Currently, I am struggling with this calculation.
      • Zeros are accounted for in daily user counts by using zn(lookup(count distinct),0).

       

      The issue:

      • Zeros are only factored (padded into) average calculations when "Day" is selected.
      • I cannot find a successful way to isolate the grand total column that for each selected time interval.
      • If possible, I would like to calculate daily/weekly/monthly/yearly averages by number of days that should be in that interval, even if there is no data for that day. For example, if today is Monday,1/28/13, and there is no data for 1/27/13, then the weekly average should count distinct users for 1/27 to 1/28, and divide by 2 (number of days thus far in the week).

       

      Attached are the corrects counts/averages via Excel and the Tableau packaged workbook. Thank you so much for any help or insight you can provide!

       

      - Pete