How to display a group title and "0" when the aggregation for that group is zero

I have a group that groups by team. Team 1, Team 2, etc. I have a summary at the top with a COUNTDISTINCT aggregation that counts how many games the team played. I have a filter on the data that only shows the games that were played within the past 7 days. 
 
Summary looks like this:
 
Team 1 - 3
Team 3 - 2
Team 4 - 1
 
If the team did not play a game during the past week, it doesn't show up on the report at all. I want it to be able to show up in the summary, with a '0' next to the team name. 
 
Any tips on how to do this? Thanks a bunch!

Find more posts tagged with

Comments

pricher

Hi,
 
You need to put your filter (only games played during the last 7 days) at the aggregation level. In the attached example, I have created a data set with two columns: Team (String) and Game (Date). The table is grouped by Team and the aggregation is a COUNT (you can use COUNTDISTINCT too) where the filter is applied:

micajblock

You are going to have to create 2 data sets. One with all the teams. The other with your current query. Then create a joined dataset between the 2 using an outer join. That way there will always be data for all the teams.

raineyt

Thank you very much Pierre, this did the trick!