Methods for Period Over Period (PoP) Analysis in Looker

  • 23 June 2022
  • 0 replies
  • 2638 views

Userlevel 3

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Looker can implement period-over-period (PoP) analysis with a number of methods that vary in functionality and modeling difficulty. These factors need to be balanced depending on requirements and expertise. This article addresses eight methods for performing PoP analysis, outlined in the chart below. Use the chart to determine the appropriate method for your use case, and select the name of the desired PoP analysis method in the Method implementations section below:
 

Method implementations


Select the name of the desired PoP analysis method below to be directed to the implementation instructions. A majority of the examples use an order_items table from an e-commerce dataset. The example LookML view below is the basis of these examples:

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. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects.
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
}

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]
}
}

1. Any Two Native Timeframes

2. Any Two Native Timeframes (with Liquid)

3. Current Period and Previous Period

4. Current Period and Many Previous Periods

5. Current Period and Any Arbitrary Period

6. Any Two Arbitrary Periods

7. Arbitrary Period and Directly Previous Period

8. Arbitrary Period and Many Previous Periods

 


0 replies

Be the first to reply!

Reply