Period-over-Period Date Comparisons

I found two super cool blocks for doing PoP comparisons - Date Comparison Block by bencannon for Datatonic and Flexible Period-over-Period Block by fabio for LookerIO - but was super bummed to find out that neither would quite meet the needs I had. So I tried my hand at making a hybrid that I could work into my Looker setup and came up with something I think is pretty neat. It’s very flexible and does not require you to create any new views of your data. Details on its functionality are commented in the code itself and briefly explained on the GitHub page linked below.

It doesn’t handle comparing a specific date range to another specific range like the Datatonic block, so I’d recommend that utility if you need to compare ranges of different lengths or time periods that aren’t at regular intervals.

So, introducing for the first time, Looker PoP Comparison . This is my first attempt at a Looker block like this, and I had way less time than I wanted to test it, so please feel free to tear it apart! Any feedback or suggestions are welcome! I’m hoping to beef up the documentation when I have some more free time.

Dimension%20Screenshot

7 Likes

Annnnd I already found a data type problem. My intended fix made it worse. Working on it now!

ETA: Resolved!

Hello Caitlink, thank you for you great work!! However I experienced some difficulties integrating the model part. I see you connected everything directly to Big Query, we do the same thing connecting looker to our data Warehouse on Big Query, but when I’m integrating your model part, I have the following error message:
Would you be able to help me on that ?
Thanks a lot !
Théo

You cannot copy and paste the whole file - the code shouldn’t contain all the fake data I put in there like the label, description, or “foo bar” where clause. Where the comment says “Add the clause shown after the AND to your explore”, only copy the if statement that comes after the word “AND”. It also seems to think the fee value you’re filtering is a dimension and not a measure, but that could be because of the fake restriction still in there. I’d delete that first and see what happens.

Hello Caitlin. Thank you for posting this helpful article.

I was able to migrate almost all of your logic from BigQuery to Snowflake…with 1 exception…

view: _pop_compare {
  label: "PoP Comparison"
  derived_table: {
    sql:
      SELECT
        periods.period_num
        ,anchors.anchor_segment
      FROM (select seq4() as period_num from table(generator(rowcount => {% parameter num_comparison_periods %}))) as periods
      CROSS JOIN
        (select seq4() as anchor_segment from table(generator(rowcount => (select datediff({% parameter anchor_breakdown_type %},TO_DATE({% date_start anchor_date_range %}),TO_DATE({% date_end anchor_date_range %})))
              ))) as anchors
      ;;
  }

However the line table(generator(rowcount => (select datediff({% parameter anchor_breakdown_type %},TO_DATE({% date_start anchor_date_range %}),TO_DATE({% date_end anchor_date_range %}))) fails because in Snowflake, generator needs a constant, not a calculation.

The Snowflake database encountered an error while running this query.

SQL compilation error: argument 1 to function GENERATOR needs to be constant, found ‘(SELECT DATE_DIFFDATEINDAYS(2019-08-27, 2019-09-26) AS “DATEDIFF(DAY,TO_DATE(DATEADD(‘DAY’, -29, CURRENT_DATE())),TO_DATE(DATEADD(‘DAY’, 30, DATEADD(‘DAY’, -29, CURRENT_DATE()))))” FROM (VALUES (null)) DUAL)’

Have you or anyone else tried to recreate your PoP comparison in Snowflake with any success?

1 Like

I’ve never worked with Snowflake, so unfortunately I’m not much help. Does Snowflake allow for variables, temp tables, or CTEs? Finding the value ahead of time then joining it into the range generator or something like that might be doable.

1 Like

Great questions. Snowflake does allow variables and temp tables…however I don’t know how to run multiple sql commands in a looker derived table.

Do you know a way I could run 2 sql statements in a looker derived table (or some other snippit of code in looker)? If I can do that, I can use a variable to resolve my issue.

I got it working in Snowflake!!! I used the “Create Process” feature in Looker to run a step to create a varable, then a second step to build the dynamic date range table…

I can share my view and explore with you if you want to post a Snowflake compliant version of your code. Your work was tremendously helpful, and I’d be happy to share what I have to help you expand the systems your solution can reach!

I forked your code and added my version that is snowflake-compatible.

1 Like

Awesome, thanks! I’m super swamped at work at the moment, but when things slow down, I can take a look. Three cheers for dialect translation!

1 Like