How to use liquid variables date dimension and forecasting

[AlinaZaprudska]

Hello! I'm using this way to choose data aggregation in my current Looker solution:
 

  dimension_group: created {
    type: time
    # hidden: no
    can_filter: no
    timeframes: [date, week, month]
    sql: TIMESTAMP(${TABLE}.atDate);;
  }

  parameter: date_aggregation_parameter {
    hidden: no
    type: unquoted
    label: "Choose data aggregation"
    description: "It is a level of date aggregation, possible values: [Daily, Weekly, Monthly]"

    allowed_value: {
      label: "Daily"
      value: "DAY"
    }
    allowed_value: {
      label: "Weekly"
      value: "ISOWEEK"
    }
    allowed_value: {
      label: "Monthly"
      value: "MONTH"
    }
  }

  dimension: date {
    hidden: no
    # can_filter: no
    type: string
    label_from_parameter: date_aggregation_parameter
    label: "New Date"
    sql:
    {% if date_aggregation_parameter._parameter_value == 'DAY' %}
      ${created_date}
    {% elsif date_aggregation_parameter._parameter_value == 'ISOWEEK' %}
      ${created_week}
    {% elsif date_aggregation_parameter._parameter_value == 'MONTH' %}
      ${created_month}
    {% endif %};;
  }

but unfortunately it doesn't work with data forecasting.
I got this message: Forecasting requires a single date dimension, remove or pivot additional dimensions in your query.

Is there a way not to change date structure and be able to use forecasting at the same time? 

​​​​​​​thanks in advance!

[Kris_MTLA]

I am not too sure, but from the sound of your error it is not due to your date dimension .. it is more due to the fact that your are trying to include more than 1 dimension in your query. 

Looker forecasting only allows for 1 single dimension, here is a quote from the Looker docs:

• Include exactly one dimension, which must be a timeframe dimension, with dimension fill enabled. Sort your results by this timeframe dimension in descending order.

If this is at all helpful, please feel free to reference the blog post I wrote on Looker forecasting here

[Dawid]

It’s because the “date” is a string. It is possible to implement dynamic timeframe that results in date every time you select different granularity but you need to use a calendar table for that

[AlinaZaprudska]

It’s because the “date” is a string. It is possible to implement dynamic timeframe that results in date every time you select different granularity but you need to use a calendar table for that

hey @Dawid !
thank you. Yes, it's related to string format of the dynamic timeframe I use. 

maybe there are any useful links how to create dynamic timeframe using a calendar table? 

[Dawid]

Actually I have finished it here in my GitBook:

https://moseleyi.gitbook.io/looker/knowledge-base/dynamic-timeframe

[asliceoftom]

@Dawid  I don't think that link is accessible outside your organisation? 

For me I can forecast when I choose a timeframe other than date, and get this same error if I use 1 date dim, and 1 measure. 

[Dawid]

Hi @asliceoftom 

It just changed its place in my gitbook, it's personal so no organisation attached to it. You can find it here: https://data-ecosystem.gitbook.io/looker/knowledge-base/dynamic-timeframe

Later on I used it to create many other dimension that would interact with these two parameters and created fully changeable visusalisation, so that one parameter (day, month) would control the groups and another "granularity detail" another level like pivot.  And everything would dynamically change, hence switching from Last 6 months monthly to Last 6 weeks weekly took two clicks:

  dimension: timeframe_vis_label {
    label: "Timeframe Label"
    description: "Use it if you want the current timeframe to be labelled as 'Current'"
    type: string
    sql: IFF(${TABLE}.{%- parameter granularity -%}_offset = 0, 'Current',

                  {%- if granularity._parameter_value == "day" -%}
                    CONCAT(MONTHNAME(${date}), ' ', ${day_of_month})

                  {%- elsif granularity._parameter_value == "week" -%}
                    CONCAT(MONTHNAME(${first_day_of_week}), ' ', EXTRACT(DAY FROM DATE(${first_day_of_week})))

                  {%- elsif granularity._parameter_value == "month" -%}
                    CONCAT(TO_CHAR(${first_day_of_month_string}, 'MMMM'), IFF(${month} = 1, CONCAT(' ', ${year}), ''))

                  {%- elsif granularity._parameter_value == "quarter" -%}
                    CONCAT('Q', ${quarter}, ' ', ${year})

                  {%- elsif granularity._parameter_value == "year" -%}
                    ${year}::STRING
                  {%- endif -%}
      ) ;;
    order_by_field: timeframe
  }

  dimension: timeframe_to_date {
    view_label: "Dimensions"
    description: "Use only with Static Metrics"
    type: yesno
    sql:  {%- if granularity._parameter_value == "day" -%}
            1=1
          {%- else -%}
            ${TABLE}.is_before_day_of_{%- parameter granularity -%}
          {%- endif -%} ;;
  }

It included labels for visualisation or yesno filters to only use "M/W/Y to date"