Including Zeros in Aggregated Distinct Counts and Grand Total Averages
Pete Staley Jan 29, 2013 4:15 PMNew 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

DAU Sample 1.xlsx 9.8 KB

DAU Sample2.twbx.zip 146.4 KB