Show groups that have no data

[#1]

Hi there,

I am creating a report that summarizes the daily counts of visitors in each department. The way I have set this up is using the grouping expert to pull the departments and then I've used a formula to do the unique counts of visitors. However, the issue is that on some days there may be no visits in a specific department that therefore that department wont show (this is generally fine) but i need it to display this time so another calculated field (pre-covid baseline) displays.

Is there any way to possibly display all the departments regardless of any customer visits?

Some visual representation of what i want:

Department | Customer Count | Pre-COVID Count

Sporting | 25 | 33

Houseware | 10 | 22

Toys | 11 | 5

Clothing | No Data | 3

From the above, if I run the report the clothing group wont display because there was no visit data to pull. However, can i possibly have the report just show a 0 or no data ?

Thanks in advance!

[DellSC]

Link FROM the full department list TO the visit data using a left outer join. Group on the department from the department list - NOT the department from the visit data.

-Dell

[ido_millet]

Use an Outer Join or any other approach (e.g. UNION) to ensure all departments are in the data set.

[former_member1074186]

I have tried a left outer as well as a full outer join with no success.

I will attempt the using a Union and see if that works.