Bar chart missing data for first date bar

jfonte

Hello,

When trying to create a bar chart that shows averages by week I am receiving an empty bar even though there is data for that week. I'm using the filter 'is in the last 4 weeks' but when I change it to is in range my visualization populates. I have other tiles with the same lookml that are not having this issue.

marybells

Hi @jfonte ! Thank you for your question! We would recommend the following actions if you have not completed.

Test using same date field in the same explore for a different metric. It is very possible you did this in your second screenshot but we can't confirm based on the screenshots.
Check the SQL in Explore. You can compare dates between "in the last 4 weeks" and "in the range" to understand if there is any underlying discrepancy.
If you continue to have issues, we recommend reaching out to Looker Support! This may require further investigation by our support engineers.

jfonte

@marybells Thanks for your reply! I've tried the first 2:

1. Comparing the SQL with a metric that works the only change is the filter for the activity type.

2. Testing the SQL for the filter it seems in the past 4 weeks only does a lookback for 21 days vs using the range.

Filter on past 4 weeks

WHERE ((( units_per_hour_aggregates."INTERVAL_START_TIMESTAMP"  ) >= ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', -21, TO_DATE(DATEADD('day', (0 - EXTRACT(DOW FROM DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))::integer), DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ)))))) AS TIMESTAMP_NTZ)))) AND ( units_per_hour_aggregates."INTERVAL_START_TIMESTAMP"  ) < ((CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(DATEADD('day', 28, DATEADD('day', -21, TO_DATE(DATEADD('day', (0 - EXTRACT(DOW FROM DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))::integer), DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_NTZ))))))) AS TIMESTAMP_NTZ))))))

Filter for in range

WHERE ((( units_per_hour_aggregates."INTERVAL_START_TIMESTAMP"  ) >= (CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(TO_TIMESTAMP('2024-04-20 00:00') AS TIMESTAMP_NTZ))) AND ( units_per_hour_aggregates."INTERVAL_START_TIMESTAMP"  ) < (CONVERT_TIMEZONE('America/New_York', 'UTC', CAST(TO_TIMESTAMP('2024-05-20 10:04') AS TIMESTAMP_NTZ)))))

Still having the same issue with the first bar being null.