Question

How to use liquid variables date dimension and forecasting

  • 21 June 2022
  • 4 replies
  • 73 views

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!


4 replies

Userlevel 7
Badge +1

Actually I have finished it here in my GitBook:

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

 

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? 

Userlevel 7
Badge +1

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

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

Reply