Conditionally multiplying a numeric filter

We have an implementation of Period-over-Period analysis that we’ve paired with a date granularity filter so that the user can select the granularity of the period-ago, the number of periods ago to compare, and the date granularity to display via filters. Below is a subset of the relevant bits of code.

The first filter fills in the blank of ___-over-___ analysis.

parameter: period_size {
group_label: " Required Parameters"
label: "PoP Period Offset Size"
description: "REQUIRED FOR PoP. Determines the size of the period-ago. (day/week/month/quarter/year ago)"
type: unquoted
allowed_value: {value:"Day"}
allowed_value: {value:"Week"}
allowed_value: {value:"Month"}
allowed_value: {value:"Quarter"}
allowed_value: {value:"Year"}
default_value: "Year"
}

The second filter allows the user to input which periods ago to query...

  filter: periods_ago_to_include {
group_label: " Required Parameters"
label: "PoP Periods Ago To Include"
description: "REQUIRED FOR PoP. Determines the number of periods ago to include."
type: number
default_value: "0,1"
}

...from a collection of placeholders below

dimension: pop_periods_ago {
sql:
...
SELECT periods_ago FROM (
SELECT 0 AS periods_ago
{% for i in (1..365)%} UNION ALL SELECT {{i}}{%endfor%}
) possible_periods
WHERE
{%condition pop.periods_ago_to_include%}periods_ago{%endcondition%}
...
}

The below parameter allows the user to change the date granularity in the output.

parameter: date_granularity {
type: unquoted
group_label: "Optional Parameters"
group_item_label: "Dynamic Date Granularity"
description: "Pair this with Dynamic Date. Dictates which date grain is used."
allowed_value: { value: "Day" }
allowed_value: { value: "Week"}
allowed_value: { value: "Month" }
allowed_value: { value: "Year" }
}

Currently, if a user wants to perform a YoY analysis with a monthly or yearly date granularity, the user selects “Year” period size, “0,1” periods ago, and either “Month” or “Year” the analysis queries 0 and 1 years ago with the chosen date granularity.

However, we’d like this to function differently under a certain condition. If the user selects “Year” period size, “0,1” periods ago, and either “Day” or “Week” we want the function to instead behave as if it was given the filters “Week ago”, “0 and 52 periods ago”, and either “Day” or “Week”.

IOW, if the user selects day or week date granularity we’d like our YoY analysis to change the interval from 1 year to 52 weeks without the user explicitly changing the filter settings.

All of this set-up to ask the question:

Is it possible to multiply the filter periods_ago_to_include inside the below condition by 52?

{%condition pop.periods_ago_to_include%}periods_ago{%endcondition%}
0 0 113
0 REPLIES 0
Top Labels in this Space
Top Solution Authors