Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Period-over-Period Date Comparisons

CaitlinK
Userlevel 3

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

Look%20Screenshot

19 replies

CaitlinK
Userlevel 3

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


ETA: Resolved!

T

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

48

CaitlinK
Userlevel 3

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.

Dan_Montgomery

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?

CaitlinK
Userlevel 3

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.

Dan_Montgomery

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.

Dan_Montgomery

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!

Dan_Montgomery

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



CaitlinK
Userlevel 3

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!

adam.minton
Rank

Hey Caitlin,


Really easy PoP solution to implement. I tested both for Snowflake and BigQuery and I noticed that Looker disables the measure drill when you do PoP. Did you experience the same issue?


Thanks!

Abtin

@CaitlinK thank you for the work here, this is awesome!


@Dan_Montgomery many thanks as well for your Snowflake variant 🙏

Regarding the issue which you had raised about generator requiring a constant, I had implemented a simple udf a while back which we use in a couple of places with a particular pattern to get around this.


CREATE OR REPLACE FUNCTION "GENERATE_SERIES_ARRAY"(N FLOAT)

RETURNS ARRAY

LANGUAGE JAVASCRIPT

COMMENT='Takes a double N and returns an array of size N with a generated series from 0 to N-1'

AS '

  return Array.from({length: N}, (v, k) => k+0);

';

With the above, I’ve modified your method so not to have to rely on sql_step and any kind of persistence, like so:


  derived_table: {

    sql:

        SELECT

          periods.value AS period_num

          , anchors.value + 1 AS anchor_segment

        FROM LATERAL FLATTEN(input => generate_series_array({% parameter num_comparison_periods %})) AS periods

        , LATERAL FLATTEN(input =>

            generate_series_array(

              datediff({% parameter anchor_breakdown_type %},{% date_start anchor_date_range %},{% date_end anchor_date_range %})

            )

          ) anchors

Wanted to share in case it’d be helpful!

A

@adam.minton Did you have to make any changes to the code? When trying to run the query on Snowflake I am getting an error.

Looker Error

adam.minton
Rank

Hi Alan,


I did not make any changes to the code, it appears Kaitlin is the latest person to make an update about 9 months ago. Based on the error, it looks like you need to add a sql_trigger or datagroup to the derived table where the error is occurring.

A

Can you explain to why this is necessary to use?


sql_always_where: {% if period_over_period.anchor_date_range._is_filtered %}

${period_over_period.period_num} IS NOT NULL

{% else %} 1 = 1

{% endif %}

;;


If I add this to my explore, it doesn’t allow me to run queries without always including the ‘anchor_date_range’ parameter.


If I don’t want to require this, does it matter if I remove it from the explore?

Jeff3000

@CaitlinK, thank you so much for posting this, and thank you to @Dan_Montgomery for the Snowflake conversion.


I’m having an issue with the code not respecting the week_start_day and fiscal_month_offset parameters. Specifically, even though I’ve set the week_start_day to “Sunday”, and the fiscal_month_offset = 1, natural language in the Anchor Date Range filter like “last week” and “this fiscal quarter” still resolve to a week beginning date of Monday and a quarter begin date of 2020-07-01 (should be 2020-05-01).


Any thoughts on where I would look in your code to address this?


Thanks in advance!

bianc_jay

Thanks @CaitlinK and @Dan_Montgomery for setting this up! It was super helpful. I forked the code and added updates that made it compatible with Redshift.



If anyone has any suggestions on how to optimize this version please let me know!

catchvivek94

I am trying to add this code to my looker and I am gettin this error.
Any idea what might cause this?

 

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

 

 

 

 



favicon.ico

GitHub
 

 

 

 


12673723?s=400&v=4

 

 

 

 

 

Hello can you please help me with this issue?
 

 

 

danrmonty/Looker-PoP-Comparison

 

 

 

 

 

 

 

 

SNOWFLAKE VERSION: A code block that can be used to build Looker reports that compare period over period data - danrmonty/Looker-PoP-Comparison

 

 

 

 

 

 

 

 

 

 

 

 






 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

N

Is anyone running into issues with BQ? I am getting a ‘variable not found’ error in the explore.

N

Getting a bunch of errors -

Unknown view '_pop_compare' 

Could not find a field named "_pop_compare.period_num"

Could not find a field named "_pop_compare.anchor_segment"

Reply


Terms of Service