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.
- 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).
- 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!