Create a view based on daily snapshot between other date filters

Hello guys,

I have a very big challenge in front of me, that I am somehow stuck on Looker:

  • What do I need to see? Per day, how many active subscriptions do I have?

  • What is my criteria? A subscription is only active if it has subscription_start_date before today and subscription_end_date after today.

  • I am currently using Impala as a engine for Looker.

My view looks like the following:

view: subscriptions {
  sql_table_name: subscriptions ;;
  suggestions: no

  dimension_group: subscription_start_date {
    label: "Subscription Start Date"
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.subscription_start_date ;;
  }

  dimension_group: subscription_end_date {
    label: "Subscription End Date"
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.subscription_end_date ;;
  }

 dimension: subscription_id {
    type: string
    sql: ${TABLE}.subscription_id;;
  }

 measure: count_unique_subscription_id {
    label: "Count Unique Subscription Id"
    group_label: "Measures"
    sql: ${subscription_id};;
    type: count_distinct
  }
}

So, for each date I need to perform a count of unique subscription_id that starts in the past and ends in the future.

I thought about creating a derived table that would bring me a list of dates between a range and work on top of that, but sadly impala does not help me.

Did anyone face a similar challenge before and could help me out?

Cheers :slight_smile:

Hi @hbrandao,

Could you possibly set up a custom dimension that takes into account current date, so logically the dimension would look something like:

dimension: is_in_range
sql: CASE WHEN subscription_start_date < DATE(CURRENT_DATE() ) AND subscription_end_date > DATE(CURRENT_DATE()) THEN yes ELSE no END

and then filter using this?

Feel free to shout up if you need any more clarity on this!

Thanks, Adam

Hey @adstott90

That would work if I wanted to see only a current status of my subscriptions.
I want however to create one data point for each day in the past, so the Current_Date() would always need to be pushed back in time.

As you can see below, the measure is applied for each day which subscription start and end dates range accordingly.

@hbrandao

Ah yes I see, my bad! For the type of situation you’re mentioning we tend to use scheduled queries in BigQuery to provide a snapshot per day (I’m not familiar with Impala at all sorry!), so you could possibly think about doing something similar, and using the “updated date” in the logic to provide a count per day? Or even structure a separate table that counts using this logic at this stage (so applying the logic before it is in Looker essentially)?

Again I’m not familiar with Impala or obviously your set up so you may have already tried this. The only thing is I’d imagine it could get quite data heavy so that might be something to think about.

Thanks, Adam

1 Like

@hbrandao it sounds like your first idea to create a date table was right. Were you having trouble creating it or having performance issues using it?

I don’t know much about Impala, but if you have a way to load data from a CSV (or similar), this Stack Overflow question has a couple options for creating a date table: https://stackoverflow.com/questions/40000079/create-a-date-table-in-hive.

1 Like

Hey @michael_zearn
I finally was able to achieve what I wanted. I created a static table on impala with several years of dates and connected to my explore.
Surprisingly the calculation is quick and accurate.

Thanks for the documentation!