Results != SQL Runner Results

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 Solved
0 2 177
1 ACCEPTED 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?

View solution in original post

2 REPLIES 2

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!!

Top Labels in this Space
Top Solution Authors