Looking for a solution to count unique working days in Looker with existing parameters

[Looking_for_a_s]

Hello Looker community,

I'm working on a project where I need to count the unique working days based on data in a Google Sheet. The Google Sheet has the following structure:

A - Name B - Date C - GT Type (LD\SR\OD\else) D - Images E - Boxes F - Set Name (could be many) G - Task Type (tagging\fixing\ filtering\other) H - Notes (anything they want to add)

I have created several parameters in Looker, including a "working days" parameter. The current formula I'm using is COUNT(DISTINCT(date)). However, this counts the working days twice if there are two reports a day, while it should count it as only one working day.

Unfortunately, I haven't been able to find a solution for this issue using Looker's built-in functions. I tried using TRUNC_DAYS, but it's not supported in Looker.

I'm looking for a solution that allows me to count distinct working days using the existing parameters in Looker without modifying the underlying Google Sheet data. If anyone has encountered a similar problem or knows a solution, your help would be greatly appreciated!

Thank you in advance!

[Roderick]

Hi there,

Here is a solution that allows you to count distinct working days using the existing parameters in Looker without modifying the underlying Google Sheet data:

1. Create a new parameter called "Working Days".
2. Set the expression for the Working Days parameter to the following:

Code snippet

DATE_TRUNC('day', date)

Use code with caution. Learn more

3. Use the Working Days parameter in your count formula instead of the date column.

For example, if your count formula is currently:

Code snippet

COUNT(DISTINCT(date))

Use code with caution. Learn more

Change it to:

Code snippet

COUNT(DISTINCT(Working Days))

Use code with caution. Learn more

This will count the unique working days, even if there are multiple reports on the same day.

I hope this helps! Let me know if you have any other questions.