Hello,
This one is a head scratcher.
I have a table (VW_DATE) with one column (DATE) - Snowflake. The table is populated with a sequential list of dates - 1/1/2000 thru 1/1/2100. I want to get every 7th day starting at a base date (example below starts at 3/5/2020). I updated the explore with:
sql_always_where: MOD(DATE_PART(EPOCH_SECONDS,'3/5/2020'::DATE) - DATE_PART(EPOCH_SECONDS,${vw_date.date_raw}), 7) = 0;;
Now, when I Explore on the Date view and select just the Date column, I’m getting a sequential list of date, no 7 day increments. HOWEVER, if I take the generated SQL (see below) and run it via the RUNNER, the results are as expected 2020-03-05, 2020-03-12, 2020-03-19….etc. I posted the SQL in a variety of other tools, and get the expected results as well.
What am I doing wrong? Any help appreciated.
Thanks,
-paul
RAW SQL
SELECT
TO_CHAR(TO_DATE(vw_date."DATE" ), 'YYYY-MM-DD') AS "vw_date.date_date"
FROM "ANALYTICS"."VW_DATE"
AS vw_date
WHERE MOD(DATE_PART(EPOCH_SECONDS,'3/5/2020'::DATE) - DATE_PART(EPOCH_SECONDS,(vw_date."DATE")), 7) = 0
GROUP BY TO_DATE(vw_date."DATE" )
ORDER BY 1 DESC
LIMIT 500
Solved! Go to Solution.
I bet you that “Dimension Fill” is toggled on, and it’s kindly filling in the missing dates for you, unaware that you’re intentionally excluding them.
https://docs.looker.com/exploring-data/visualizing-query-results#filling_in_missing_dates_and_values
if you turn off “fill in missing dates” per the screenshot in that docs page, do the results look like what you’d expect?
I bet you that “Dimension Fill” is toggled on, and it’s kindly filling in the missing dates for you, unaware that you’re intentionally excluding them.
https://docs.looker.com/exploring-data/visualizing-query-results#filling_in_missing_dates_and_values
if you turn off “fill in missing dates” per the screenshot in that docs page, do the results look like what you’d expect?
Thanks, @izzymiller - that was it!!