Knowledge Drop

Snowflake examples: WTD, MTD, QTD, YTD analysis structure

  • 7 July 2021
  • 1 reply
  • 239 views

Userlevel 4

Last tested: Jun 30, 2020
 

Check out Methods for period over period (PoP) analysis in Looker for more methods and examples - this is an example of Method 2.

An example framework for "to day" type analysis (this is specific to Snowflake, so the date functions will need to be adapted to the dialect you are leveraging).

View File Logic:

parameter: time_period {

type: unquoted

allowed_value: {

value: "WTD"

}

allowed_value: {

value: "MTD"

}

allowed_value: {

value: "QTD"

}

allowed_value: {

value: "YTD"

}

}

dimension: is_wtd {

type: yesno

hidden: yes

sql: ${created_raw} > TO_DATE(DATE_TRUNC('week', CURRENT_DATE())) AND ${created_raw} < TO_DATE(DATEADD('day', 7, DATE_TRUNC('week', CURRENT_DATE()))) ;;

}

 

dimension: is_mtd {

type: yesno

hidden: yes

sql: ${created_raw}> TO_DATE(DATE_TRUNC('month', CURRENT_DATE())) AND ${created_raw} < TODATE(DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE()))) ;;

}

dimension: is_qtd {

type: yesno

hidden: yes

sql: ${created_raw} > TO_DATE(DATE_TRUNC('quarter', CURRENT_DATE())) AND ${created_raw} < (TO_DATE(DATEADD('month', 3, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', CURRENT_DATE()) AS DATE)) AS DATE)))) ;;

}

dimension: is_ytd {

type: yesno

hidden: yes

sql: ${created_raw} > TO_DATE(DATE_TRUNC('year', CURRENT_DATE())) AND ${created_raw} < TO_DATE(DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE()))) ;;

}

 

Explore/Model File logic:

explore: inventory_items {

always_filter: {

filters: {

field: time_period

value: "MTD"

}

}

sql_always_where:

{% if inventory_items.time_period._parameter_value == 'WTD' %} ${is_wtd}

{% elsif inventory_items.time_period._parameter_value == 'MTD' %} ${is_mtd}

{% elsif inventory_items.time_period._parameter_value == 'QTD' %} ${is_qtd}

{% elsif inventory_items.time_period._parameter_value == 'YTD' %} ${is_ytd}

{% else %} 1=1

{% endif %}

;;

}

If you want a consolidated approach without having to create a dimension for each "to day" function please note the liquid syntax below.

View File:

parameter: time_period {

type: unquoted

allowed_value: {

value: "WTD"

}

allowed_value: {

value: "MTD"

}

allowed_value: {

value: "QTD"

}

allowed_value: {

value: "YTD"

}

}

 

dimension: to_day {

label: "{% parameter time_period %}"

type: yesno

hidden: yes

sql:

{% if time_period._parameter_value == 'WTD' %}

${created_raw} > TO_DATE(DATE_TRUNC('week', CURRENT_DATE()))

AND

${created_raw} < TO_DATE(DATEADD('day', 7, DATE_TRUNC('week', CURRENT_DATE())))

{% elsif time_period._parameter_value == 'MTD' %}

${created_raw} > TO_DATE(DATE_TRUNC('month', CURRENT_DATE()))

AND

${created_raw} <TO_DATE(DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())))

{% elsif time_period._parameter_value == 'QTD' %}

${created_raw} > TO_DATE(DATE_TRUNC('quarter', CURRENT_DATE()))

AND

${created_raw} < (TO_DATE(DATEADD('month', 3, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', CURRENT_DATE()) AS DATE)) AS DATE))))

{% elsif time_period._parameter_value == 'YTD' %}

${created_raw} > TO_DATE(DATE_TRUNC('year', CURRENT_DATE()))

AND

${created_raw} < TO_DATE(DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())))

{% else %}

1=1

{% endif %};;

}

 

Explore/Model File:

explore: inventory_items {

always_filter: {

filters: 

field: time_period

value: "MTD"

}

}

sql_always_where:

${to_day}

;;

}

 

This content is subject to limited support.                

 

 


1 reply

Userlevel 1

In case anyone wants code which would give you the values only until the given date instead of current date, use a another parameter and as below and change the code as below. The code above if MTD is chosen in OCT and if the date is given as 09/10 instead of 10/05 would return all values for September and sometimes we would like to see values only upto a given date

parameter: to_date {
   type: date
 }

  parameter: time_period {
    type: unquoted

    allowed_value: {

      value: "WTD"

    }

    allowed_value: {

      value: "MTD"

    }

    allowed_value: {

      value: "QTD"

    }
    allowed_value: {
      
      value: "Last6Months"
      
    }

    allowed_value: {

      value: "YTD"

    }

  }

  dimension: to_day {

    label: "{% parameter time_period %}"

    type: yesno

    hidden: no

    sql:

    {% if time_period._parameter_value == 'WTD' %}

    ${AsofDate} >= TO_DATE(DATE_TRUNC('week', {% parameter Date.to_date %}))

    AND
    ${AsofDate} <=  {% parameter Date.to_date %}

    

    {% elsif time_period._parameter_value == 'MTD' %}

    ${AsofDate} >= TO_DATE(DATE_TRUNC('month', {% parameter Date.to_date %}))

    AND
    
    ${AsofDate} <=  {% parameter Date.to_date %}

    {% elsif time_period._parameter_value == 'QTD' %}

    ${AsofDate} >= TO_DATE(DATE_TRUNC('quarter', {% parameter Date.to_date %}))

    AND
    
    ${AsofDate} <=  {% parameter Date.to_date %}

   
    {% elsif time_period._parameter_value == '6Months' %}

    ${AsofDate} <=  {% parameter Date.to_date %}

    AND

    ${AsofDate} >=({% parameter Date.to_date %}) - INTERVAL '6 month'

    {% elsif time_period._parameter_value == 'YTD' %}

    ${AsofDate} >= TO_DATE(DATE_TRUNC('year', {% parameter Date.to_date %}))

    AND
    
    ${AsofDate} <=  {% parameter Date.to_date %}

    {% else %}

    1=1

    {% endif %};;

    }

Reply