PoP Method 1: Use Looker's Native Date Dimension Groups

This is the simplest method for implementing PoP analysis in Looker. It uses two native date timeframes and a pivot. Alternatively, you can use a yesno field or custom filter for period-to-date comparison logic.

This method is easy to model; however, if you desire any level of user interactivity, your users will need explore permissions and the ability to navigate and assemble Explore analyses.

Using this method, users can perform analyses like the ones below.

Year-over-year (grouped by month, pivoted by year):

Month-over-month (grouped by day of month, pivoted by month):

Advantages and Disadvantages

Advantage:

• Users can self-serve PoP analyses in an Explore. Developing additional fields (unless you create a yesno LookML field on which to filter, as in the examples above) or Liquid implementation is not required.

Disadvantages:

• The type of comparison (e.g., year-over-year, month-over-month) cannot be changed without choosing different fields in an Explore.
  • This can be time-consuming and potentially confusing for users to do themselves in an Explore and is impossible for users without explore permissions.
  • Importantly, this means it is impossible to control the PoP analysis using dashboard filters, as they can only be applied to a single field.
• This method does not easily support filtered measures. For example, logic such as "% change vs previous period" must be achieved with a table calculation, rather than with a measure.

Method

1. Choose the measure(s) to plot in an Explore.
2. Choose one date timeframe to group the measure by (e.g., day of month). See the created dimension_group in the LookML example below.
3. Choose a second date dimension timeframe to pivot by (e.g., month).
4. Apply a filter to the pivoted date dimension, and filter for the past two periods (e.g., past two months). See the month-over-month example above.
   • (Step 4 Alternatives) You may also wish to implement period-to-date logic, such as month-to-date and year-to-date, to your PoP analysis. This can be achieved with yesno LookML fields with which to filter. See the mtd_only and ytd_only dimensions in the LookML example below. Or, you can apply period-to-date logic with a custom Explore filter.

LookML Example

Below is an example view file with the fields referenced above:

The SQL in the sql parameters below is for Redshift. Be sure to adapt the example to your database dialect.

    
###  Period over Period Method 1: Use Looker's native date dimension groups

view: order_items {
    sql_table_name: public.order_items ;;


    dimension: id {
        primary_key: yes
        hidden: yes
        type: number
        sql: ${TABLE}.id ;;
    }

    dimension_group: created {
        type: time
        view_label: "_PoP"
        timeframes: [
        raw,
        time,
        hour_of_day,
        date,
        day_of_week,
        day_of_week_index,
        day_of_month,
        day_of_year,
        week,
        week_of_year,
        month,
        month_name,
        month_num,
        quarter,
        year
        ]
        sql: ${TABLE}.created_at ;;
        convert_tz: no
    }


#(Method 1a) you may also wish to create MTD and YTD filters in LookML

    dimension: wtd_only {
        group_label: "To-Date Filters"
        label: "WTD"
        view_label: "_PoP"
        type: yesno
        sql:  (EXTRACT(DOW FROM ${created_raw}) < EXTRACT(DOW FROM GETDATE())
                OR
            (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
                OR
            (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
            EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE())))  ;;
    }

    dimension: mtd_only {
        group_label: "To-Date Filters"
        label: "MTD"
        view_label: "_PoP"
        type: yesno
        sql:  (EXTRACT(DAY FROM ${created_raw}) < EXTRACT(DAY FROM GETDATE())
                OR
            (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
                OR
            (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
            EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE())))  ;;
    }

    dimension: ytd_only {
        group_label: "To-Date Filters"
        label: "YTD"
        view_label: "_PoP"
        type: yesno
        sql:  (EXTRACT(DOY FROM ${created_raw}) < EXTRACT(DOY FROM GETDATE())
                OR
            (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
                OR
            (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM GETDATE()) AND
            EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
            EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE())))  ;;
    }

    measure: count {
        label: "Count of order_items"
        type: count
        hidden: yes
    }
    measure: count_orders {
        label: "Count of orders"
        type: count_distinct
        sql: ${order_id} ;;
        hidden: yes
    }

    measure: total_sale_price {
        label: "Total Sales"
        view_label: "_PoP"
        type: sum
        sql: ${sale_price} ;;
        value_format_name: usd
        drill_fields: [created_date]
    }
}

# ---------- EXPLORE ----------- #

explore: order_items {
    label: "PoP Method 1: Use Looker's native date dimension groups"
  }

See the How to do Period-over-Period Analysis Help Center article for another example of implementing this method using custom filters.

Period-over-Period Method 2: Allow Users to Choose Periods with Parameters

The native implementation of Method 1: Use Looker's Native Date Dimension Groups may not work out-of-the-box for many use cases. For instance, you may need to allow view-only users to change the analysis by using dashboard filters, or you may need to offer business users a more guided Explore experience.

This additional functionality is possible with interactive dimensions that users can manipulate with parameter filters to change the desired comparisons.

Here is an example grouped by month, year-over-year:

Here is an example grouped by day of month, month-over-month:

Advantages and Disadvantages

Advantages:

• Minimal modeling is required.
• View-only users can manipulate the analysis on a dashboard.
• The user experience is simplified and only requires changing filter values, rather than changing Explore fields.

Disadvantages:

• Filtered measures are incompatible with this method.
• Custom time periods (anything that is not a dimension_group timeframe) are not supported.

Method

1. Create a parameter to define options for dimension grouping (e.g., group by day, week, month). See the choose_breakdown parameter in the LookML example below.
2. Create another parameter for selecting a pivot dimension (e.g., pivot by month, year, etc.). See the choose_comparison parameter in the LookML below.
3. Create dimensions that change according to each parameter value, and use Liquid "if" statements to define the timeframe to use for each parameter value selection. See the pop_row and pop_pivot dimensions in the LookML below. Pro-tips:
   • Adding a label_from_parameter parameter ensures that the resulting visualization will be labeled according to the chosen parameter values.
   • Visualizations can be sorted according to the selected dimension value. Create hidden dimensions to sort month and day names by their numerical or index counterparts, and then use the order_by_field parameter to reference the sort-by dimensions within the interactive dimensions. See the sort_by1 and sort_by2 dimensions in the LookML example below.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 2: Allow users to choose periods with parameters

include: "method1.view.lkml"

view: pop_simple {
    extends: [order_items]

    parameter: choose_breakdown {
    label: "Choose Grouping (Rows)"
    view_label: "_PoP"
    type: unquoted
    default_value: "Month"
    allowed_value: {label: "Month Name" value:"Month"}
    allowed_value: {label: "Day of Year" value: "DOY"}
    allowed_value: {label: "Day of Month" value: "DOM"}
    allowed_value: {label: "Day of Week" value: "DOW"}
    allowed_value: {value: "Date"}
    }

    parameter: choose_comparison {
    label: "Choose Comparison (Pivot)"
    view_label: "_PoP"
    type: unquoted
    default_value: "Year"
    allowed_value: {value: "Year" }
    allowed_value: {value: "Month"}
    allowed_value: {value: "Week"}
    }

    dimension: pop_row  {
    view_label: "_PoP"
    label_from_parameter: choose_breakdown
    type: string
    order_by_field: sort_hack1 # Important
    sql:
    {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_name}
    {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
    {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
    {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week}
    {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
    {% else %}NULL{% endif %} ;;
    }

    dimension: pop_pivot {
    view_label: "_PoP"
    label_from_parameter: choose_comparison
    type: string
    order_by_field: sort_hack2 # Important
    sql:
    {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
    {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_name}
    {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
    {% else %}NULL{% endif %} ;;
    }


    # These dimensions are just to make sure the dimensions sort correctly
    dimension: sort_by1 {
    hidden: yes
    type: number
    sql:
    {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_num}
    {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
    {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
    {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week_index}
    {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
    {% else %}NULL{% endif %} ;;
    }

    dimension: sort_by2 {
    hidden: yes
    type: string
    sql:
    {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
    {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_num}
    {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
    {% else %}NULL{% endif %} ;;
    }
}

# ---------- EXPLORE ---------- #

explore: pop_simple {
    label: "PoP Method 2: Allow users to choose periods with parameters"
    always_filter: {
      filters: [choose_comparison, choose_breakdown]
  }

Period-over-Period Method 3: Custom Choice of Current and Previous Periods with Parameters

This method allows users to select a "previous period," which is a period of the same length immediately preceding the current selected period.

This method uses a similar approach to Method 2: Allow Users to Choose Periods with Parameters; but, instead of using parameters to select the appropriate date timeframe, the logic for the correct previous period is defined using Liquid. See the examples below:

Explore grouped by date, comparing sales in the past month to sales in the previous period of the same length:

Explore using filtered measures, without a pivot on the period to compare:

Advantages and Disadvantages

Advantages:

• Performs the same function as Method 2: Allow Users to Choose Periods with Parameters.
• Supports filtered measures.
• Provides granular control of period definition without requiring too many fields, which may confuse the user.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• Overlapping periods are not supported (this requires a join).
• Only a single date field may be used (e.g., the same functionality for other date fields requires replicating all the code, per date field).

Method

1. Create a filter field for the user to select the current period. See the current_date_range field in the LookML example below.
   • Apply a sql parameter to the filter to exclude any dates outside the chosen date range.
2. Create a parameter for selecting a previous period type (e.g., previous week, previous year, etc.). See the compare_to parameter in the LookML example below.
3. Create hidden "helper" dimensions for calculating periods:
   • Create a dimension to calculate the number of days in the current period. See the days_in_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • Create a dimension to calculate the start and end dates for the previous period. See the period_2_start and period_2_end dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • Create a dimension to calculate the ordinal value of each day in the current and previous periods (1st day, 2nd day...) to "line up" the two periods. See the day_in_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • (Optional) Create a dimension to correctly order the pivot columns. See the order_for_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • (Optional) Create a dimension to provide static labels to be used in filtered measures. See the period_filtered_measures under TO CREATE FILTERED MEASURES in the LookML example below.

Next, create the dimensions to be selected in the Explore:

1. Create a dimension_group that will be used as the grouping rows. See the date_in_period dimension_group under DIMENSIONS TO PLOT in the LookML example below.
   • This is constructed from the start date of the current period and the ordinal value of each day in the period.
2. Create a pivot dimension that will group the dates into the current or previous period and provide user-friendly labels. See the period dimension under DIMENSIONS TO PLOT in the LookML example below.
   • If the date is between the current_date_range filter values, then it is in the current period. If the date is between the previous period start and previous period end dates, it is in the previous period.
3. (Optional) To make filtered measures based on these custom periods, use either "last" (for the previous period) or "this" (for the current period) as the filter expression. See current_period_sales and previous_period_sales under TO CREATE FILTERED MEASURES in the LookML example below.
   • An additional measure can be created to calculate percent change period-over-period with the custom period filtered measures. See the sales_pop_change measure under TO CREATE FILTERED MEASURES in the LookML example below.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 3: Custom choice of current and previous periods with parameters

# Like Method 2, but instead of using parameters to simply select the appropriate date dimension,
# we will use liquid to define the logic to pick out the correct periods for each selection.


include: "method1.view.lkml"

view: pop_parameters {
    extends: [order_items]


    filter: current_date_range {
    type: date
    view_label: "_PoP"
    label: "1. Current Date Range"
    description: "Select the current date range you are interested in. Make sure any other filter on Event Date covers this period, or is removed."
    sql: ${period} IS NOT NULL ;;
    }

    parameter: compare_to {
    view_label: "_PoP"
    description: "Select the templated previous period you would like to compare to. Must be used with Current Date Range filter"
    label: "2. Compare To:"
    type: unquoted
    allowed_value: {
        label: "Previous Period"
        value: "Period"
    }
    allowed_value: {
        label: "Previous Week"
        value: "Week"
    }
    allowed_value: {
        label: "Previous Month"
        value: "Month"
    }
    allowed_value: {
        label: "Previous Quarter"
        value: "Quarter"
    }
    allowed_value: {
        label: "Previous Year"
        value: "Year"
    }
    default_value: "Period"
    view_label: "_PoP"
    }



## ------------------ HIDDEN HELPER DIMENSIONS  ------------------ ##

    dimension: days_in_period {
    hidden:  yes
    view_label: "_PoP"
    description: "Gives the number of days in the current period date range"
    type: number
    sql: DATEDIFF(DAY, DATE({% date_start current_date_range %}), DATE({% date_end current_date_range %})) ;;
    }

    dimension: period_2_start {
    hidden:  yes
    view_label: "_PoP"
    description: "Calculates the start of the previous period"
    type: date
    sql:
        {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -${days_in_period}, DATE({% date_start current_date_range %}))
        {% else %}
        DATEADD({% parameter compare_to %}, -1, DATE({% date_start current_date_range %}))
        {% endif %};;
    }

    dimension: period_2_end {
    hidden:  yes
    view_label: "_PoP"
    description: "Calculates the end of the previous period"
    type: date
    sql:
        {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -1, DATE({% date_start current_date_range %}))
        {% else %}
        DATEADD({% parameter compare_to %}, -1, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
        {% endif %};;
    }

    dimension: day_in_period {
    hidden: yes
    description: "Gives the number of days since the start of each period. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
    type: number
    sql:
    {% if current_date_range._is_filtered %}
        CASE
        WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
        THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${created_date}) + 1
        WHEN ${created_date} between ${period_2_start} and ${period_2_end}
        THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1
        END
    {% else %} NULL
    {% endif %}
    ;;
    }

    dimension: order_for_period {
    hidden: yes
    type: number
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
            THEN 1
            WHEN ${created_date} between ${period_2_start} and ${period_2_end}
            THEN 2
            END
        {% else %}
            NULL
        {% endif %}
        ;;
    }

 ## ------- HIDING FIELDS  FROM ORIGINAL VIEW FILE  -------- ##    

    dimension_group: created {hidden: yes}
    dimension: ytd_only {hidden:yes}
    dimension: mtd_only {hidden:yes}
    dimension: wtd_only {hidden:yes}


## ------------------ DIMENSIONS TO PLOT ------------------ ##

    dimension_group: date_in_period {
    description: "Use this as your grouping dimension when comparing periods. Aligns the previous periods onto the current period"
    label: "Current Period"
    type: time
    sql: DATEADD(DAY, ${day_in_period} - 1, DATE({% date_start current_date_range %})) ;;
    view_label: "_PoP"
    timeframes: [
        date,
        hour_of_day,
        day_of_week,
        day_of_week_index,
        day_of_month,
        day_of_year,
        week_of_year,
        month,
        month_name,
        month_num,
        year]
    }


    dimension: period {
    view_label: "_PoP"
    label: "Period"
    description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period' or 'Previous Period'"
    type: string
    order_by_field: order_for_period
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
            THEN 'This {% parameter compare_to %}'
            WHEN ${created_date} between ${period_2_start} and ${period_2_end}
            THEN 'Last {% parameter compare_to %}'
            END
        {% else %}
            NULL
        {% endif %}
        ;;
    }


## ---------------------- TO CREATE FILTERED MEASURES ---------------------------- ##

    dimension: period_filtered_measures {
    hidden: yes
    description: "We just use this for the filtered measures"
    type: string
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %} THEN 'this'
            WHEN ${created_date} between ${period_2_start} and ${period_2_end} THEN 'last' END
        {% else %} NULL {% endif %} ;;
    }

# Filtered measures

    measure: current_period_sales {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price};;
    filters: [period_filtered_measures: "this"]
    }

    measure: previous_period_sales {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price};;
    filters: [period_filtered_measures: "last"]
    }

    measure: sales_pop_change {
    view_label: "_PoP"
    label: "Total Sales period-over-period % change"
    type: number
    sql: CASE WHEN ${current_period_sales} = 0
            THEN NULL
            ELSE (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 END ;;
    value_format_name: percent_2
    }

}

# ---------- EXPLORE ---------- #

explore: pop_parameters {
    label: "PoP Method 3: Custom choice of current and previous periods with parameters"
    always_filter: {
      filters: [current_date_range: "6 months", compare_to: "Year" ]
        }
}

Period-over-Period Method 4: Compare Multiple Templated Periods

This method is an extension of Method 3: Custom Choice of Current and Previous Periods with Parameters. Please be sure to familiarize yourself with Method 3 before moving forward with implementing Method 4.

Sometimes you may want to compare more than just the current and previous periods. By adding a new parameter to choose the number of periods to compare, we can allow this; however, it does add a lot of verbosity to the model.

Below is an example looking at the current and previous three periods:

Advantages and Disadvantages

Advantages:

• Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
• Can accommodate more than two period comparisons in a PoP analysis.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• The developer must decide how many periods to create parameter values for, which can potentially limit user analysis.
• Overlapping periods are not supported (this requires a join).
• Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).

Method

1. Create a parameter with allowed values to choose the number of periods to compare. See the comparison_periods parameter in the LookML example below.
2. Create two hidden dimensions to calculate the periods for each of the allowed_value subparameters — one for the start of each period, and one for the end of each period. See the period_3_start, period_3_end, period_4_start, and period_4_end dimensions below.

   Period two is defined in the extended view. See period_2_start and period_2_end under HIDDEN HELPER DIMENSIONS in the Method 3 LookML example.

3. Adjust the period dimension in the LookML example below according to how many periods are available to the user for comparison. This field displays the correct label, depending on the number of periods selected by the user.
4. Create a hidden dimension to order periods correctly by numerical value (e.g., period 1, period 2, period 3). See the order_for_period dimension in the LookML example below.
5. Apply the parameter (comparison_periods in the LookML example below) in a sql_always_where in the Explore. See the pop_parameters_multi_period `explore` in the LookML example below.
6. (Optional) Similar to Method 2, create additional (hidden) dimensions to correctly sort strings, such as month and day of week names, by their numerical or index counterparts. See day_in_period dimension in the LookML example below.
7. (Optional) New filtered measures can be created for each possible previous period. See the examples under TO CREATE FILTERED MEASURES in the Method 3 LookML example.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the pop_parameters view referenced in Method 3, which extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 4: Compare multiple templated periods

# Like Method 3, but expanded to compare more than two periods

include: "method3.view.lkml"
# This extended version allows you to choose multiple periods (this can also work in conjunction with the custom range version, or separately)

view: pop_parameters_multi_period {
    extends: [pop_parameters]

    parameter: comparison_periods {
    view_label: "_PoP"
    label: "3. Number of Periods"
    description: "Choose the number of periods you would like to compare."
    type: unquoted
    allowed_value: {
        label: "2"
        value: "2"
    }
    allowed_value: {
        label: "3"
        value: "3"
    }
    allowed_value: {
        label: "4"
        value: "4"
    }
    default_value: "2"
    }

    dimension: period_3_start {
    view_label: "_PoP"
    description: "Calculates the start of 2 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -(2 * ${days_in_period}), DATE({% date_start current_date_range %}))
    {% else %}
        DATEADD({% parameter compare_to %}, -2, DATE({% date_start current_date_range %}))
    {% endif %};;
    hidden: yes

    }

    dimension: period_3_end {
    view_label: "_PoP"
    description: "Calculates the end of 2 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -1, ${period_2_start})
    {% else %}
        DATEADD({% parameter compare_to %}, -2, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
    {% endif %};;
    hidden: yes
    }

    dimension: period_4_start {
    view_label: "_PoP"
    description: "Calculates the start of 4 periods ago"
    type: date
    sql:
    {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -(3 * ${days_in_period}), DATE({% date_start current_date_range %}))
    {% else %}
        DATEADD({% parameter compare_to %}, -3, DATE({% date_start current_date_range %}))
    {% endif %};;
    hidden: yes
    }

    dimension: period_4_end {
    view_label: "_PoP"
    description: "Calculates the end of 4 periods ago"
    type: date
    sql:
        {% if compare_to._parameter_value == "Period" %}
        DATEADD(DAY, -1, ${period_2_start})
        {% else %}
        DATEADD({% parameter compare_to %}, -3, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
        {% endif %};;
    hidden: yes
    }

    dimension: period {
    view_label: "_PoP"
    label: "Period"
    description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period', 'Previous Period' or '3 Periods Ago'"
    type: string
    order_by_field: order_for_period
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
            THEN 'This {% parameter compare_to %}'
            WHEN ${created_date} between ${period_2_start} and ${period_2_end}
            THEN 'Last {% parameter compare_to %}'
            WHEN ${created_date} between ${period_3_start} and ${period_3_end}
            THEN '2 {% parameter compare_to %}s Ago'
            WHEN ${created_date} between ${period_4_start} and ${period_4_end}
            THEN '3 {% parameter compare_to %}s Ago'
            END
        {% else %}
            NULL
        {% endif %}
        ;;
    }

    dimension: order_for_period {
    hidden: yes
    view_label: "Comparison Fields"
    label: "Period"
    type: string
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
            THEN 1
            WHEN ${created_date} between ${period_2_start} and ${period_2_end}
            THEN 2
            WHEN ${created_date} between ${period_3_start} and ${period_3_end}
            THEN 3
            WHEN ${created_date} between ${period_4_start} and ${period_4_end}
            THEN 4
            END
        {% else %}
            NULL
        {% endif %}
        ;;
    }
    dimension: day_in_period {
    description: "Gives the number of days since the start of each periods. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
    type: number
    sql:
    {% if current_date_range._is_filtered %}
        CASE
        WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
        THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${created_date}) + 1

        WHEN ${created_date} between ${period_2_start} and ${period_2_end}
        THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1

        WHEN ${created_date} between ${period_3_start} and ${period_3_end}
        THEN DATEDIFF(DAY, ${period_3_start}, ${created_date}) + 1

        WHEN ${created_date} between ${period_4_start} and ${period_4_end}
        THEN DATEDIFF(DAY, ${period_4_start}, ${created_date}) + 1
        END

    {% else %} NULL
    {% endif %}
    ;;
    hidden: yes
    }
}

# ---------- EXPLORE ---------- #

explore: pop_parameters_multi_period {
    label: "PoP Method 4: Compare multiple templated periods"
    extends: [pop_parameters]
    sql_always_where:
        {% if pop_parameters_multi_period.current_date_range._is_filtered %} {% condition pop_parameters_multi_period.current_date_range %} ${created_raw} {% endcondition %}
        {% if pop_parameters_multi_period.previous_date_range._is_filtered or pop_parameters_multi_period.compare_to._in_query %}
        {% if pop_parameters_multi_period.comparison_periods._parameter_value == "2" %} 
            or ${created_raw} between ${period_2_start} and ${period_2_end}
        {% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "3" %} 
            or ${created_raw} between ${period_2_start} and ${period_2_end} 
            or ${created_raw} between ${period_3_start} and ${period_3_end}
        {% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "4" %} 
            or ${created_raw} between ${period_2_start} and ${period_2_end} 
            or ${created_raw} between ${period_3_start} and ${period_3_end} or ${created_raw} between ${period_4_start} and ${period_4_end}
        {% else %} 1 = 1
        {% endif %}
        {% endif %}
        {% else %} 1 = 1
        {% endif %};; 
}

Period-over-Period Method 5: Compare Current Period with Another Arbitrary Period

This method is an extension of Method 3: Custom Choice of Current and Previous Periods with Parameters. Please be sure to familiarize yourself with Method 3 before moving forward with implementing Method 5.

Sometimes you don't want to compare the current period to exactly the previous period. Adding another filter to Method 3 allows users to compare the current period with any other arbitrary date range.

Below is an example looking at the current period against a custom previous date range:

Advantages and Disadvantages

Advantages:

• Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
• A custom date range can be compared against the current period chosen — two arbitrary date ranges can be plotted side by side.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• The developer must decide how many custom periods to allow, which can potentially limit user analysis. The LookML example below only allows for one custom date range to compare against the current period.
• Overlapping periods are not supported (this requires a join).
• Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).

Method

1. Create a filter field for the user to select the previous date range they want to compare against. See the previous_date_range filter field in the LookML example below.
2. Create hidden dimensions to calculate the start and end of the user-selected previous period. See the period_2_start and period_2_end dimensions in the LookML example below.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the pop_parameters view referenced in Method 3, which extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 5: Compare current period with another arbitrary period

# Like Method 3, but allows you to compare the current period with any other arbitrary date range period


include: "method3.view.lkml"

## This extended version allows the user to also choose a custom date range for the previous period

view: pop_parameters_with_custom_range {
    extends: [pop_parameters]

    # custom date range
    filter: previous_date_range {
    type: date
    view_label: "_PoP"
    label: "2a. Previous Date Range (Custom):"
    description: "Select a custom previous period you would like to compare to. Must be used with Current Date Range filter."
    }

    parameter: compare_to {label: "2b. Compare To:"}
    dimension_group: date_in_period {hidden:yes}

    dimension: period_2_start {
    view_label: "_PoP"
    description: "Calculates the start of the previous period"
    type: date
    sql:
        {% if compare_to._in_query %}
            {% if compare_to._parameter_value == "Period" %}
            DATEADD(DAY, -${days_in_period}, DATE({% date_start current_date_range %}))
            {% else %}
            DATEADD({% parameter compare_to %}, -1, DATE({% date_start current_date_range %}))
            {% endif %}
        {% else %}
            {% date_start previous_date_range %}
        {% endif %};;
    hidden:  yes
    }

    dimension: period_2_end {
    hidden:  yes
    view_label: "_PoP"
    description: "Calculates the end of the previous period"
    type: date
    sql:
        {% if compare_to._in_query %}
            {% if compare_to._parameter_value == "Period" %}
            DATEADD(DAY, -1, DATE({% date_start current_date_range %}))
            {% else %}
            DATEADD({% parameter compare_to %}, -1, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
            {% endif %}
        {% else %}
            {% date_end previous_date_range %}
        {% endif %};;
    }
}

# ---------- EXPLORE ---------- # 

explore: pop_parameters_with_custom_range {
    label: "PoP Method 5: Compare current period with another arbitrary period"
    always_filter: {
      filters: [current_date_range: "1 month", previous_date_range: "2 months ago for 2 days"]
    }
  }

Period-over-Period Method 6: Compare Two Arbitrary Date Ranges

Sometimes an analysis requires a comparison between two arbitrary periods, not including the current period. This is relevant for use cases such as web analytics, where, for example, the number of registrations for an upcoming webinar needs to be compared to a previous webinar from six months ago. The challenge is that the date ranges might have a different number of days, so the periods need to be aligned based on their start day.

Below is an example of a period-over-period analysis with custom periods:

Below is an example of a filtered measure based on custom periods made possible by this method:

Advantages and Disadvantages

Advantage:

• This type of analysis is familiar to Google Analytics users and is a great choice to use for a similar use case.

Disadvantages:

• Both comparison date ranges must be set manually, which may seem slower and more complex to users who expect a simpler PoP analysis.
• There are other additional nuances that may potentially frustrate users:
  • The first period must occur before the second period.
  • An additional filter is required to ensure that the 'First Period' and 'Second Period' labels are not null.
• Overlapping periods are not supported (this requires a join).

Method

1. Create a filter field for the first date range. See first_period_filter under USER FILTERS in the LookML example below.
2. Create a filter field for the second date range. See second_period_filter under USER FILTERS in the LookML example below.
3. Create two hidden dimensions to calculate the number of days between the start dates of the two filters and a relevant date in the dataset. See the days_from_start_first and days_from_start_second dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.
4. Create a dimension that checks the grouping date against the start of the second and first filter periods — this ensures that the two periods are lined up based on their start date. See the days_from_first_period dimension under DIMENSIONS TO PLOT in the LookML example below.
5. Create a dimension that uses the hidden dimensions from step 2 to label each day as either 'First Period' or 'Second Period' for the Explore analysis pivot. See the period_selected dimension under DIMENSIONS TO PLOT in the LookML example below.

   Labeling won't work if your periods overlap. There are no joins, so each row can only be in the current or the previous period.

6. Add an always_filter parameter to the explore, set to filter the pivot field with the condition -NULL. This ensures that there will be no results outside the custom date ranges specified.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 6: Compare two arbitrary date ranges

# Like Method 5, but allowing arbitrary definition of the 'current' period
# provides functionality like Google Analytics, which allows you to compare two arbitrary date ranges


include: "method1.view.lkml"

view: pop_arbitrary {
    extends: [order_items]


## ------------------ USER FILTERS  ------------------ ##

    filter: first_period_filter {
    view_label: "_PoP"
    group_label: "Arbitrary Period Comparisons"
    description: "Choose the first date range to compare against. This must be before the second period"
    type: date
    }

    filter: second_period_filter {
    view_label: "_PoP"
    group_label: "Arbitrary Period Comparisons"
    description: "Choose the second date range to compare to. This must be after the first period"
    type: date
    }

## ------------------ HIDDEN HELPER DIMENSIONS  ------------------ ##

    dimension: days_from_start_first {
    view_label: "_PoP"
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start first_period_filter %}, ${created_date}) ;;
    }

    dimension: days_from_start_second {
    view_label: "_PoP"
    hidden: yes
    type: number
    sql: DATEDIFF('day',  {% date_start second_period_filter %}, ${created_date}) ;;
    }

## ------------------ DIMENSIONS TO PLOT ------------------ ##

    dimension: days_from_first_period {
    view_label: "_PoP"
    description: "Select for Grouping (Rows)"
    group_label: "Arbitrary Period Comparisons"
    type: number
    sql:
        CASE
        WHEN ${days_from_start_second} >= 0
        THEN ${days_from_start_second}
        WHEN ${days_from_start_first} >= 0
        THEN ${days_from_start_first}
        END;;
    }


    dimension: period_selected {
    view_label: "_PoP"
    group_label: "Arbitrary Period Comparisons"
    label: "First or second period"
    description: "Select for Comparison (Pivot)"
    type: string
    sql:
        CASE
            WHEN {% condition first_period_filter %}${created_raw} {% endcondition %}
            THEN 'First Period'
            WHEN {% condition second_period_filter %}${created_raw} {% endcondition %}
            THEN 'Second Period'
            END ;;
    }

## Filtered measures

    measure: current_period_sales {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price};;
    filters: [period_selected: "Second Period"]
    }

    measure: previous_period_sales {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price};;
    filters: [period_selected: "First Period"]
    }

    measure: sales_pop_change {
    view_label: "_PoP"
    label: "Total sales period-over-period % change"
    type: number
    sql: (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 ;;
    value_format_name: percent_2
    }

    dimension_group: created {hidden: yes}
    dimension: ytd_only {hidden:yes}
    dimension: mtd_only {hidden:yes}
    dimension: wtd_only {hidden:yes}
}

# ---------- EXPLORE ---------- ##

explore: pop_arbitrary {
label: "PoP Method 6: Compare two arbitrary date ranges"
always_filter: {
    filters: [first_period_filter: "NOT NULL", second_period_filter: "NOT NULL", period_selected:"-NULL"]
 }
}

Period-over-Period Method 7: Compare Any Period with the Previous Preceding Period of the Same Length

This method enables users to select any date range and compare it to the previous period of the same length. For example, if a user selects a range of 12 days in the past, the data will be compared to 12 days directly preceding that range. This is particularly useful for single value visualization comparisons.

Below is an example of a single value visualization comparison between a custom period and the period of the same length directly preceding it:

Advantages and Disadvantages

Advantages:

• The previous period dynamically matches the interval of the selected date range.
• Limited Liquid is required in the implementation.
• Filtered measures are supported.
• The date range filter can be used on dashboards so that view-only users can manipulate the filter values.

  When you are applying the filter to a dashboard: If there is another date filter on the dashboard, make sure that the tiles required to update with the PoP analysis listen to the PoP date filter.

Disadvantage:

• This method does not allow for gaps between periods; only directly preceding periods can be compared.

Method

1. Create a date filter field for users to enter a date or date range for a Look or dashboard. See the date_filter field in the LookML example below.
2. Create two hidden fields of type: time to convert the start date and end date entered by the user into raw dates. A raw timeframe will allow flexibility in the interval calculation in the following step. In order to account for the possibility that a user selects "is before" logic in the date filter, the start date and end date fields wrap the filter logic in a CASE statement that will make the start date "2013-01-01" (this date can be a Unix epoch, "1970-01-01", or you can use the first date in your database). The same CASE logic is used when we convert the end date of the filter to account for the user selecting "is on or after" (where the end date is the current date). See the filter_start_date and filter_end_date dimensions in the LookML example below.
3. Create a hidden dimension to calculate the interval, or difference, between the filter start date and end date. See the interval dimension in the LookML example below.
4. Create a hidden dimension to calculate the start date of the previous period by subtracting the interval dimension from the start date. See the previous_start_date dimension in the LookML example below.
5. Create a field to label the two time periods "Selected Period" and "Previous Period" in the user-facing content. The previous period is the period between the previous start date and the start of the selected period (the previous period ends when the selected period begins). See the timeframes dimension in the LookML example below.
   • The logic indicating whether a date is within the selected period or previous period is defined in two dimensions of type: yesno that are then referenced in the timeframes dimension to label the period appropriately. The logic in yesno dimensions uses an existing date field, ${created_date}, and can be reused to create filtered measures. See is_current_period and is_previous_period under For Filtered Measures in the LookML example below.
   • Alternatively, code the logic from the yesno fields directly into the CASE statement in the timeframes dimension.
6. (Optional) Create filtered measures. See selected_period_order_revenue and previous_period_order_revenue under For Filtered Measures in the LookML example below.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

###  Period over Period Method 7: Compare any period with the previous preceding period of the same length

# Like Method 2, but here we define any date filter (not just current) and
# compare it to the previous period of the same length - great for single value visualization comparisons

include: "method1.view.lkml"
view: pop_previous {
    extends: [order_items]


    filter: date_filter {
    view_label: "_PoP"
    description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering"
    type: date
}

dimension_group: filter_start_date {
    hidden: yes
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2013-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
}

dimension_group: filter_end_date {
    hidden: yes
    type: time
    timeframes: [raw,date]
    sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
}

dimension: interval {
    hidden: yes
    type: number
    sql: datediff(day, ${filter_start_date_raw}, ${filter_end_date_raw});;
}

#start date of the previous period
dimension: previous_start_date {
    hidden: yes
    type: string
    sql: DATEADD(day, - ${interval}, ${filter_start_date_raw});;
}


dimension: timeframes {
    view_label: "_PoP"
    type: string
    case: {
    when: {
        sql: ${is_current_period} = true;;
        label: "Selected Period"
    }
    when: {
        sql: ${is_previous_period} = true;;
        label: "Previous Period"
    }
    else: "Not in time period"
    }
}

## For filtered measures


    dimension: is_current_period {
    hidden: yes
    type: yesno
    sql: ${created_date} >= ${filter_start_date_date} AND ${created_date} < ${filter_end_date_date} ;;
    }

    dimension: is_previous_period {
    hidden: yes
    type: yesno
    sql: ${created_date} >= ${previous_start_date} AND ${created_date} < ${filter_start_date_date} ;;
    }

measure: selected_period_order_revenue {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price} ;;
    filters: [is_current_period: "yes"]
    value_format_name: decimal_1
}
measure: previous_period_order_revenue {
    view_label: "_PoP"
    type: sum
    sql: ${sale_price} ;;
    filters: [is_previous_period: "yes"]
    value_format_name: decimal_1
}

    dimension: ytd_only {hidden:yes}
    dimension: mtd_only {hidden:yes}
    dimension: wtd_only {hidden:yes}

}

# ---------- EXPLORE ---------- #

explore: pop_previous {
    label: "PoP Method 7: Compare any period with the previous period"
    always_filter: {
      filters: [date_filter]
    }
     sql_always_where: ${timeframes} <>'Not in time period' ;;
  }
  
    

Period-over-Period Method 8: Compare Any Period to Any Other Period

This method allows users to choose the current period, the grouping for that period, which period to compare (pivot), and how many periods to compare. This is the most complex PoP method, but it is also the most flexible.

Below is an example with order items in the current month-to-date, broken down by day and compared to last month:

Below is an example with an arbitrary date range, broken down by day and compared to the same date range for the previous three years:

Advantages and Disadvantages

Advantages:

• The date range can be broken to a more granular level (e.g., months, days, weeks).
• You can be very specific about which prior periods to compare, and you can quickly compare multiple periods at once (e.g., "x periods ago" or "past y periods").
• This method defines possible previous periods in bulk. This is in contrast to Method 4, where previous periods are defined manually and individually.
• The joins in this method allow for overlapping period comparison.
• The LookML for this method lives in its own explore, so it is kept separate from any complexity in the rest of the model.
• Although the initial setup is time consuming, the approach is modular and relatively easy to scale to add new date dimensions. You will only need a single new derived table and join for each date dimension. To achieve this, make a new view with a derived table for each new date, add the desired measures to the derived table definition, and join the new view to the explore. See the pop_order_items_created and pop_order_items_delivered views in the LookML example below.
  • The structure is similar to joining different views to a central date dimension table. This makes it quick to compare different aggregations for the same period without triggering symmetric aggregates, which can limit expensive queries.

Disadvantages:

• This method requires its own explore, whereas other approaches can be incorporated into a single view file.
  • Any complex data transformations or joins required for the explore need to be replicated for each new date added, as the model is scaled.
  • Users must navigate to a new Explore solely for PoP analysis.
• Measures need to be defined in advance, rather than existing measures being reused. Any existing logic would need to be replicated, as the model is scaled to incorporate new dates.
• The LookML is complex; maintenance and collaboration may be difficult.

Method

1. Create two views: one to configure the filters and plotted date dimensions, and a second to establish the maximum number of previous periods for comparison. These two views will remain untouched, as the method is scaled to include additional dates. See the pop and numbers views in the LookML below.
2. In the first view, create four hidden fields that become the filters users interact with, as depicted in the examples above. Two are filter fields, and two are dimensions:

   These filters are hidden to avoid cluttering the Explore field picker. The filters are surfaced in the Explore with an always_filter parameter. See the flexible_pop explore under EXPLORE in the LookML example below.

   • Filter 1 — Date Range: A filter of type: date for users to select the date range on which to filter. See the date_filter field under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below.
   • Past Periods Filter: A filter of type: number for users to choose the number of past periods to compare. If the user makes no selection, the number of past periods will default to 1. See the over_how_many_past_periods field under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below.
   • Filter 2 — Date Range Breakdown: A dimension of type: string for users to select the granularity of the selected date range (in quarters, months, weeks, days) by which to group aggregates. This dimension uses LookML case parameters to control the values the user can choose from. See the within_period_type field under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below.
   • Filter 3 — Previous Period: A second dimension of type: string for users to select the time period for comparison and the granularity by which to group aggregates. Similar to Filter 2, this dimension uses LookML case parameters to control the values the user can choose from. See the over_period_type field under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below.
3. Next, in the second maximum periods view (numbers below), create a hidden dimension of type: number to represent the number of periods. We will use this dimension in the next step. See the n dimension in the numbers view below.
4. Returning to the first view, create three dimensions:
   • One hidden dimension to serve as an order_by field. This dimension calculates the interval for the dimension to be plotted as rows and ensures it will always be ordered correctly. See the reference_date dimension under DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below.
   • A second dimension to plot for rows, with appropriate date formatting applied. This field will be ordered by the hidden dimension above. See the reference_date_formatted dimension under DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below.
   • The last dimension will be for pivoting the prior periods for comparison. It is ordered by the type: number number of periods dimension (n, in this example) from the second maximum periods view. This ensures that each previous period is ordered in the correct way. See over_periods_ago under DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below.
5. Create an explore, joining the first two views together. The maximum periods view, numbers will be joined twice — once for calculating the size of the selected current period, in the units selected by the Date Range Breakdown filter (within_period_type), and again to determine the number of previous periods, as selected by the Past Periods Filter (over_how_many_past_periods). See the within_periods and over_periods joins in the flexible_pop explore under EXPLORE in the LookML example below.
6. Now, create measures, or aggregations, based on each date field for users to compare. For example, an orders created date or an orders delivered date. This requires developing one derived table view per date field.
   • Create one view and one derived table per date field. The derived table sql definition needs to include a date for joining the view to the original explore. This join date is the date field, which is updated dynamically to match the date granularity selected in Filter 2 — Date Range Breakdown from the first view. The derived table sql definition also should contain the desired aggregations, such as COUNT and SUM. See the pop_order_items_created view under VIEWS TO EDIT in the LookML example below.
   • Define the aggregations as LookML measures that users can select and view in an Explore. See agg_1 and agg_2 in the pop_order_items_created view under VIEWS TO EDIT in the LookML example below.
   • Join the view into the explore. See the pop_order_items_created join in the flexible_pop explore under EXPLORE in the LookML example below.
   • Repeat the above steps for the additional date field aggregations.

LookML Example

All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect.

###  Period over Period Method 8: Compare any period to any other period
# the most complex implementation, but also the most flexible


#### ------------ VIEWS TO LEAVE ALONE  ------------ ###

# This view sets up the config (doesn't need editing)
view: pop {
  sql_table_name: (SELECT NULL) ;;
  view_label: "_PoP"

### ------------ (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH ------------


# Choose a date range to filter on
filter: date_filter  {
label: "1. Date Range"
hidden: yes
type: date
convert_tz: no
}

# A second filter to choose number of past periods. Defaults to 1 if this is not selected
filter: over_how_many_past_periods {
label: "Override past periods"
description: "Apply this filter to add past periods to compare to (from the default of current vs 1 period ago)"
type: number
default_value: "<=1"
}


# Choose how to break the range down - normally done with a parameter but here is a dimension
dimension: within_period_type {
label: "2. Break down date range by"
hidden: yes
type: string
#Using case just to get friendlier UI experience in filters. Otherwise, could have a no-sql filter field
case: {
    when: {
    sql: {% parameter pop.within_period_type %}='quarter' ;;
    label: "quarter"
    }
    when: {
    sql: {% parameter pop.within_period_type %}='month' ;;
    label: "month"
    }
    when: {
    sql: {% parameter pop.within_period_type %}='week' ;;
    label: "week"
    }
    when: {
    sql: {% parameter pop.within_period_type %}='day' ;;
    label: "day"
    }
    when: {
    sql: {% parameter pop.within_period_type %}='hour' ;;
    label: "hour"
    }
}
}

# Choose the previous period
# Again we use a dimension here instead of a parameter
dimension: over_period_type {
label: "3. Compare over"
hidden: yes
type: string
#Using case just to get friendlier UI experience in filters. Otherwise, could have a no-sql filter field
case: {
    when: {
    sql: {% parameter pop.over_period_type %}='year' ;;
    label: "year"
    }
    when: {
    sql: {% parameter pop.over_period_type %}='quarter' ;;
    label: "quarter"
    }
    when: {
    sql: {% parameter pop.over_period_type %}='month' ;;
    label: "month"
    }
    when: {
    sql: {% parameter pop.over_period_type %}='week' ;;
    label: "week"
    }
    when: {
    sql: {% parameter pop.over_period_type %}='day' ;;
    label: "day"
    }
}
}

### ------------ DIMENSIONS WE WILL ACTUALLY PLOT ------------


# This is the dimension we will plot as rows
# This version is always ordered correctly
dimension: reference_date {
hidden: yes
#type: date_time <-- too aggressive with choosing your string formatting for you
#type: date <-- too aggressive with truncating the time part
#convert_tz: no
#type: nothing <-- just right
sql: DATE_TRUNC({% parameter pop.within_period_type %},DATE_ADD({% parameter pop.within_period_type %},0 - ${within_periods.n} - 1,{% date_end pop.date_filter %}));;
}

# This is the version we will actually plot in the data with nice formatting
dimension: reference_date_formatted {
type: string
order_by_field: reference_date
label: "Reference date"
sql:  TO_CHAR(
    ${reference_date},
    CASE {% parameter pop.within_period_type %}
    WHEN 'year' THEN 'YYYY'
    WHEN 'month' THEN 'MON YY'
    WHEN 'quarter' THEN 'YYYY"Q"Q'
    WHEN 'week' THEN 'MM/DD/YY' --or 'YYYY"W"WW' or 'YY-MM"W"W'
    WHEN 'day' THEN 'MM/DD/YY'
    WHEN 'hour' THEN 'MM/DD HHam'
    ELSE 'MM/DD/YY'
    END)
    ;;}


# This is the dimension we will plot as pivots
    dimension: over_periods_ago  {
    label: "Prior Periods"
    description: "Pivot me!"
    sql: CASE ${over_periods.n}
                WHEN 0 THEN 'Current '||{% parameter pop.over_period_type %}
                WHEN 1 THEN ${over_periods.n}||' '||{% parameter pop.over_period_type %} || ' prior'
                ELSE        ${over_periods.n}||' '||{% parameter pop.over_period_type %} || 's prior'
                END;;
    order_by_field: over_periods.n
    }
}

# This view establishes the max number of previous periods (doesn't need editing)
view: numbers {
sql_table_name: (
            SELECT 00 as n UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL
            SELECT 03 UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL
            SELECT 06 UNION ALL SELECT 07 UNION ALL SELECT 08 UNION ALL
            SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
            SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
            SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
            SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
            SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
            SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
            SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
            SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL
            SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL
            SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL
            SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL
            SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL
            SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL
            SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL
            SELECT 51 UNION ALL SELECT 52 )
        ;;
dimension: n {
type: number
hidden:  yes
sql: ${TABLE}.n ;;
}
}

#### ------------ VIEWS TO EDIT  ------------ ###

view: pop_order_items_created {
view_label: "Order Items (By created date)"
sql_table_name: (
SELECT
    DATE_TRUNC({% parameter pop.within_period_type %}, order_items.created_at) as join_date,
    COUNT(*) as agg_1,
    SUM(order_items.sale_price) as agg_2
    FROM order_items
-- Could add templated filters here
-- OPTIONAL : Filter inner query on min/max dates (since query optimizer probably won't)
    GROUP BY 1
    ) ;;

measure:  agg_1 {
type:  number
label: "Count"
sql: SUM(${TABLE}.agg_1) ;;
}
measure:  agg_2 {
type:  number
label: "Total Sales"
sql: SUM(${TABLE}.agg_2) ;;
}
}



#### ------------ EXTENSIBLE VERSION (Multiple dates)  ------------ ###
## This is what the above looks like if we scale it to work with multiple date fields
## We make one view per date and join them all to the model

view: pop_order_items_delivered {
view_label: "Order Items (By delivered)"
sql_table_name: (SELECT
    DATE_TRUNC({% parameter pop.within_period_type %},order_items.shipped_at) as join_date,
    COUNT(*) as agg_1,
    SUM(order_items.sale_price) as agg_2
    FROM order_items
    WHERE {%condition pop_order_items_delivered.sale_price %}order_items.sale_price{% endcondition %}
    GROUP BY 1
    ) ;;
}

### ------------ EXPLORE ----------------###

explore: flexible_pop {
    label: "PoP Method 8: Flexible implementation to compare any period to any other"
    from:  pop
    view_name: pop

    # No editing needed - make sure we always join and set up always filter on the hidden config dimensions
    always_join: [within_periods,over_periods]
    always_filter: {
      filters: [pop.date_filter: "last 12 weeks", pop.within_period_type: "week", pop.over_period_type: "year"]
    }

# No editing needed
    join: within_periods {
      from: numbers
      type: left_outer
      relationship: one_to_many
      fields: []
      # This join creates fanout, creating one additional row per required period
      # Here we calculate the size of the current period, in the units selected by the filter
      # The DATEDIFF unit is in days, so if we want hours we have to multiply it by 24
      # (It might be possible to make this more efficient with a more granular function like TIMESTAMPDIFF where you can specify the interval units)
      sql_on: ${within_periods.n}
            <= (DATEDIFF( {% parameter pop.within_period_type %},{% date_start pop.date_filter %},{% date_end pop.date_filter %} ) - 1 )
             * CASE WHEN {%parameter pop.within_period_type %} = 'hour' THEN 24 ELSE 1 END;;
    }
# No editing needed
    join: over_periods {
      from: numbers
      view_label: "_PoP"
      type: left_outer
      relationship: one_to_many
      sql_on:
                  CASE WHEN {% condition pop.over_how_many_past_periods %} NULL {% endcondition %}
                  THEN
                    ${over_periods.n} <= 1
                  ELSE
                    {% condition pop.over_how_many_past_periods %} ${over_periods.n} {% endcondition %}
                  END;;
    }

# Rename (& optionally repeat) below join to match your pop view(s)
    join: pop_order_items_created {
      type: left_outer
      relationship: many_to_one
      #Apply join name below in sql_on
      sql_on: pop_order_items_created.join_date = DATE_TRUNC({% parameter pop.within_period_type %},
                      DATEADD({% parameter pop.over_period_type %}, 0 - ${over_periods.n},
                          DATEADD({% parameter pop.within_period_type %}, 0 - ${within_periods.n},
                              {% date_end pop.date_filter %}
                          )
                      )
                  );;
    }

  }