Solved

Dynamic measure with date selected by the user

  • 24 November 2020
  • 15 replies
  • 1188 views

Userlevel 1

 

Hello,

I would like to create measures with parameters allowing the user to choose the date (month and year) that he wants to display the sales data of month N / Year N and month N-1 / Year N-1 This is what i do


measure: Nb_de_jours_mois_N {
    type: count_distinct

    sql: ${TABLE}.VENTE ;;
    filters: [dte_vente_date: "last month"]
  }

  measure: Nb_de_jours_mois_N_1 {
    type: count_distinct
    sql: ${TABLE}.VENTE ;;
    filters: [vente_date:"13 months ago"]
  }

 

But I want it to display according to the date entered in the date filter.

 

Thanks

icon

Best answer by Naomi_Johnson 26 November 2020, 11:31

View original

This topic has been closed for comments

15 replies

Userlevel 2

Hi,

 

You can do this using templated filters.

If you create a date filter and then use that in a case statement within the sql, this allows the end user to dynamically choose the selected timeframe. I’ve created an example of below, using order_items and order_id dimension

filter: date_filter {
type: date
}

measure: orders_selected_month {
type: count_distinct
sql: case when {% condition date_filter %} ${created_raw} {% endcondition %} then ${order_id} end ;;
}

measure: orders_selected_month_ly {
type: count_distinct
sql: case when {% condition date_filter %} dateadd(year,1,${created_raw}) {% endcondition %} then ${order_id} end ;;
}

 

I hope this solves your query. Let me know if you have any questions.

 

Thanks,

Naomi

Userlevel 1
@Naomi_Johnson  Thank you for your answer. I didn't understand what you did.For example, I want to compare the month of October 2020 vs 0 October 2019. and I want to create a filter that allows you to choose the month N year N that we want to compare with month N-1 and year N-1

 

measure: sum_orders_month_N {
    type: sum

     sql: ${TABLE}.orders ;;
    filters: [dte_vente_date: "last month"]
  }

  measure: sum_orders_month_N_1{

      type: sum

     sql: ${TABLE}.orders ;;


    filters: [vente_date:"13 months ago"]
  }

 

Userlevel 2

Hi,

 

From what I understand, you’d like to compare October 2020 with October 2019, or August 2020 with August 2019 etc?

 

If I have understood your question correctly, you can use the code I wrote in my answer, changing:

type: count_distinct to type:sum

${created_raw} to ${vente_date}

${order_id} to ${orders}

I have rewritten it below using your columns and type: (Note: depending on the SQL dialect, you may need to change dateadd() function to the correct function for the dialect you are using)

filter: date_filter {
type: date
}

measure: orders_selected_month {
type: sum
sql: case when {% condition date_filter %} ${vente_date} {% endcondition %} then ${orders} end ;;
}

measure: orders_selected_month_ly {
type: sum
sql: case when {% condition date_filter %} dateadd(year,1,${vente_date}) {% endcondition %} then ${orders} end ;;
}

 

The templated filter date_filter appears in the Explore as a filter-only field. You can then use this to insert the user input into your SQL. Here, it’s been used to filter vente_date in the case when statement of the measure’s sql.

Here is the documentation for templated filters that may be helpful too.

 

When you go into the Explore, you will be able to filter the selected using the new date filter you have created, and then use the two new measures to see orders in selected period and orders in the same period last year.

Please see an example below, showing orders October 2020 and October 2019:

This allows the user to choose the selected date and then see the value of orders for the same period the previous year.

 

I hope that is clearer.

 

Thanks,

Naomi

Userlevel 1

Hi,

 

From what I understand, you’d like to compare October 2020 with October 2019, or August 2020 with August 2019 etc?

 

If I have understood your question correctly, you can use the code I wrote in my answer, changing:

type: count_distinct to type:sum

${created_raw} to ${vente_date}

${order_id} to ${orders}

I have rewritten it below using your columns and type: (Note: depending on the SQL dialect, you may need to change dateadd() function to the correct function for the dialect you are using)

filter: date_filter {
type: date
}

measure: orders_selected_month {
type: sum
sql: case when {% condition date_filter %} ${vente_date} {% endcondition %} then ${orders} end ;;
}

measure: orders_selected_month_ly {
type: sum
sql: case when {% condition date_filter %} dateadd(year,1,${vente_date}) {% endcondition %} then ${orders} end ;;
}

 

The templated filter date_filter appears in the Explore as a filter-only field. You can then use this to insert the user input into your SQL. Here, it’s been used to filter vente_date in the case when statement of the measure’s sql.

Here is the documentation for templated filters that may be helpful too.

 

When you go into the Explore, you will be able to filter the selected using the new date filter you have created, and then use the two new measures to see orders in selected period and orders in the same period last year.

Please see an example below, showing orders October 2020 and October 2019:

This allows the user to choose the selected date and then see the value of orders for the same period the previous year.

 

I hope that is clearer.

 

Thanks,

Naomi

 

@Naomi_Johnson  I have to validate LookML 

LookML Errors (1)

 

Toggle Error Section

There are serious errors with this LookML code that could prevent queries from running.

Variable not found "CAST".

 

Expand Occurrences

2 occurrences

tf_vente.view:754 universite_poc:tf_vente

tf_vente.view:759 univeriste_poc:tf_vente

Userlevel 2

@tatuspark I’m not sure what your generated SQL query is, so I can’t help with the error.

There are a couple of things it may be, but can’t be sure:

  • Have you accidentally typed cast when instead of case when?

  • If you change ${vente_date} to ${vente_raw}, does this solve the issue?

  • Does the SQL dialect allow for the functions I have put in the query, e.g. dateadd(year,1,date)?

 

Thanks,

Naomi

Userlevel 1

@tatuspark I’m not sure what your generate SQL query is, so I can’t help with the error.

There are a couple of things it may be, but can’t be sure:

  • Have you accidentally typed cast when instead of case when?

  • If you change ${vente_date} to ${vente_raw}, does this solve the issue?

  • Does the SQL dialect allow for the functions I have put in the query, e.g. dateadd(year,1,date)?

 

Thanks,

Naomi

@Naomi_Johnson  BigQuery

Userlevel 1

@Naomi_Johnson  My database is bigquery and here is my code

  measure: orders_selected_month {
    type: sum
    sql: case when {% condition ${date_filter} %} ${dte_vente_date} {% endcondition %} then ${ca_ht} end ;;
  }

  measure: orders_selected_month_ly {
    type: sum
    sql: case when {% condition ${date_filter} %} dateadd(year,1,${dte_vente_date}) {% endcondition %} then ${ca_ht} end ;;
  }

Userlevel 2

@tatuspark 

Please try

  measure: orders_selected_month {
type: sum
sql: case when {% condition ${date_filter} %} ${dte_vente_raw} {% endcondition %} then ${ca_ht} end ;;
}

measure: orders_selected_month_ly {
type: sum
sql: case when {% condition ${date_filter} %} date_add(date(${dte_vente_raw}), interval 1 year) {% endcondition %} then ${ca_ht} end ;;
}

You may be able to use date_add(${dte_vente_raw}, interval 1 year) instead.

 

Thanks,

Naomi

Userlevel 1

@tatuspark

Please try

  measure: orders_selected_month {
type: sum
sql: case when {% condition ${date_filter} %} ${dte_vente_raw} {% endcondition %} then ${ca_ht} end ;;
}

measure: orders_selected_month_ly {
type: sum
sql: case when {% condition ${date_filter} %} date_add(date(${dte_vente_raw}), interval 1 year) {% endcondition %} then ${ca_ht} end ;;
}

You may be able to use date_add(${dte_vente_raw}, interval 1 year) instead.

 

Thanks,

Naomi

 

 

@Naomi_Johnson  I tried everything you offered me and I still have these 2 errors

Userlevel 1

@Naomi_Johnson  the bigquery function is DATE_SUB  but i have these 2 errors

measure: orders_selected_month {
    type: sum
    sql: 
      CASE
        WHEN {% condition ${date_filtre}  %} ${dte_vente_date} {% endcondition %} 
        THEN ${ca_ht} 
      END ;;
  }

  measure: orders_selected_month_ly {
    type: sum
    sql: 
      CASE
        WHEN {% condition ${date_filtre} %}  DATE_SUB(${dte_vente_date}, INTERVAL 1 YEAR) {% endcondition %} 
        THEN ${ca_ht} 
      END ;;
  }

Userlevel 2

@tatuspark 

Hi, you need to use date_add, not date_sub, as you are adding 1 year to the dates to get the previous year.

Please also try ${dte_vente_raw} and date(${dte_vente_raw}) to see if they work, instead of ${dte_vente_date}

 

After changing these two things, if you are still getting the error, I would suggest going to the SQL section in your Explore and opening the query in SQL runner. There you can QA to find out what the error is and make the LookML change accordingly.

 

Apologies I can’t help further, as this an error in the SQL rather than the LookML and I don’t have access to your instance.

 

Naomi

Userlevel 1

@Naomi_Johnson Thanks for your help even with ${dte_vente_raw} it not working i will watch sql runner please can you help me create this dimension which contains 2 values ​​filtered from the date

TRUE = purchases only from the past year

FALSE = the rest of the purchases + purchases only from the past year

 

dimension: Category {
    sql:
      CASE
        WHEN  ${TABLE}.buy_date = "2 years ago" THEN "TRUE"
        WHEN ${TABLE}.Dbuy_date <CURRENT_DATE() THEN "FALSE"
      END
    ;;
  }

Userlevel 2

Hi, a dimension like this won’t work. This is because a dimension is like a column in your data - it can only take one value. In your query, purchases in the last year cannot be both true and false. 

 

You can use your approach, and then create two measures

measure: last_year {
type: sum
sql: ${order} ;;
filters: [category: "Yes"]
}

measure: orders {
type: sum
sql: ${order} ;;
}

the first will give you when category is true, i.e. when in the date given in your case statement

the second will give you a total sum of orders

 

Thanks,

Naomi

Userlevel 1

Hi, a dimension like this won’t work. This is because a dimension is like a column in your data - it can only take one value. In your query, purchases in the last year cannot be both true and false. 

 

You can use your approach, and then create two measures

measure: last_year {
type: sum
sql: ${order} ;;
filters: [category: "Yes"]
}

measure: orders {
type: sum
sql: ${order} ;;
}

the first will give you when category is true, i.e. when in the date given in your case statement

the second will give you a total sum of orders

 

Thanks,

Naomi

 

@Naomi_Johnson  

I don't want to do any math. i just want to group the values ​​of one table into another table like the example i showed you but i have an error because of "2 years ago" but when i put

 

dimension: Category {
    sql:
      CASE
        WHEN  ${date_ouv_date} BETWEEN '2019-01-01' AND '2019-12-31' THEN "comparable"
        WHEN ${TABLE}.DATE_OUV < CURRENT_DATE() THEN "No comparable"
      END
    ;;

 

it works but that's not what i want

I would like 

comparable = all products sold only last year

non comparable = product comparable + and the rest

 

Userlevel 1

@Naomi_Johnson  my last question please how to calculate a rank at the lookml

example : rank(${tf_vente.sum_ca},${tf_vente.sum_ca_ht})