IF with an unquotted parameter

 Hi!

 I am trying to use a IF with an unquotted parameter but it does not match. 

  dimension: period_2_start {
    hidden:  no
    view_label: "_PoPP"
    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 %}, DATE({% date_start current_date_range %}))
      {% endif %};;
  }

 Parameter

 parameter: compare_to {
    view_label: "_PoPP"
    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"

  }

Solved Solved
0 2 632
1 ACCEPTED SOLUTION

Naomi_Johnson
Participant III

Hi,

This is a really quick fix, as you’re pretty much there. There are only a couple of things to change in the dimension

  1. the double quotes around ‘Period’ in the if statement need to be single quotes
  2. the number parameter, -1, in the second dateadd function is missing

See the code below, which should work. 

dimension: period_2_start {
hidden: no
view_label: "_PoPP"
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 %};;
}

Hope I could help, and let me know if you have any questions.

Naomi

View solution in original post

2 REPLIES 2

Naomi_Johnson
Participant III

Hi,

This is a really quick fix, as you’re pretty much there. There are only a couple of things to change in the dimension

  1. the double quotes around ‘Period’ in the if statement need to be single quotes
  2. the number parameter, -1, in the second dateadd function is missing

See the code below, which should work. 

dimension: period_2_start {
hidden: no
view_label: "_PoPP"
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 %};;
}

Hope I could help, and let me know if you have any questions.

Naomi

 Hi, Naomi!

 It worked! Thanks a lot! 🙂

Top Labels in this Space
Top Solution Authors