Hi! I recently implemented Pop Analysis Method 4 (Compare Multiple Templated Periods) in an explore. It works fine except for the Period dimension. When I select the Number of Periods parameter as 2 I should be getting just This Year and Last Year as my periods (as per my understanding) but I am getting 2 Years Ago and 3 Years Ago as well in my results. See below,
Below is my LookML code,
filter: current_date_range {
view_label: "PoP Analysis (Transactions)"
label: "current_date_range_label"
description: "current_date_range_description"
type: date
sql: ${period} IS NOT NULL ;;
}
parameter: compare_to {
view_label: "PoP Analysis (Transactions)"
label: "compare_to_label"
description: "compare_to_description"
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"
}
parameter: comparison_periods {
view_label: "PoP Analysis (Transactions)"
label: "comparison_periods_label"
description: "comparison_periods_description"
type: unquoted
allowed_value: {
label: "2"
value: "2"
}
allowed_value: {
label: "3"
value: "3"
}
allowed_value: {
label: "4"
value: "4"
}
default_value: "2"
}
dimension: days_in_period {
view_label: "PoP Analysis (Transactions)"
label: "days_in_period_label"
description: "days_in_period_description"
hidden: yes
type: number
sql: DATEDIFF(DAY, DATE({% date_start current_date_range %}), DATE({% date_end current_date_range %})) ;;
}
dimension: period_2_start {
view_label: "PoP Analysis (Transactions)"
label: "period_2_start_label"
description: "period_2_start_description"
hidden: yes
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 {
view_label: "PoP Analysis (Transactions)"
label: "period_2_end_label"
description: "period_2_end_description"
hidden: yes
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: period_3_start {
view_label: "PoP Analysis (Transactions)"
label: "period_3_start_label"
description: "period_3_start_description"
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 Analysis (Transactions)"
label: "period_3_end_label"
description: "period_3_end_description"
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 Analysis (Transactions)"
label: "period_4_start_label"
description: "period_4_start_description"
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 Analysis (Transactions)"
label: "period_4_end_label"
description: "period_4_end_description"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, ${period_3_start})
{% else %}
DATEADD({% parameter compare_to %}, -3, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
hidden: yes
}
dimension: day_in_period {
view_label: "PoP Analysis (Transactions)"
label: "day_in_period_label"
description: "day_in_period_description"
type: number
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${payment_date_utc_raw} {% endcondition %}
THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${payment_date_utc_date}) + 1
WHEN ${payment_date_utc_date} between ${period_2_start} and ${period_2_end}
THEN DATEDIFF(DAY, ${period_2_start}, ${payment_date_utc_date}) + 1
WHEN ${payment_date_utc_date} between ${period_3_start} and ${period_3_end}
THEN DATEDIFF(DAY, ${period_3_start}, ${payment_date_utc_date}) + 1
WHEN ${payment_date_utc_date} between ${period_4_start} and ${period_4_end}
THEN DATEDIFF(DAY, ${period_4_start}, ${payment_date_utc_date}) + 1
END
{% else %} NULL
{% endif %}
;;
hidden: yes
}
dimension: order_for_period {
view_label: "PoP Analysis (Transactions)"
label: "order_for_period_label"
description: "order_for_period_description"
hidden: yes
type: string
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${payment_date_utc_raw} {% endcondition %}
THEN 1
WHEN ${payment_date_utc_date} between ${period_2_start} and ${period_2_end}
THEN 2
WHEN ${payment_date_utc_date} between ${period_3_start} and ${period_3_end}
THEN 3
WHEN ${payment_date_utc_date} between ${period_4_start} and ${period_4_end}
THEN 4
END
{% else %}
NULL
{% endif %}
;;
}
dimension: period {
view_label: "PoP Analysis (Transactions)"
label: "period_label"
description: "period_description"
type: string
order_by_field: order_for_period
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${payment_date_utc_raw} {% endcondition %}
THEN 'This {% parameter compare_to %}'
WHEN ${payment_date_utc_date} between ${period_2_start} and ${period_2_end}
THEN 'Last {% parameter compare_to %}'
WHEN ${payment_date_utc_date} between ${period_3_start} and ${period_3_end}
THEN '2 {% parameter compare_to %}s Ago'
WHEN ${payment_date_utc_date} between ${period_4_start} and ${period_4_end}
THEN '3 {% parameter compare_to %}s Ago'
END
{% else %}
NULL
{% endif %}
;;
}
dimension_group: date_in_period {
view_label: "PoP Analysis (Transactions)"
label: "Current Period"
description: "date_in_period_description"
type: time
sql: DATEADD(DAY, ${day_in_period} - 1, DATE({% date_start current_date_range %})) ;;
timeframes: [raw, time, hour, date, week, day_of_week, day_of_week_index, day_of_month, month, month_num, month_name, year, quarter, week_of_year]
}
Is there any issue with my LookML code?