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
Solved! Go to Solution.
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
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
@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"]
}
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
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
@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
@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
@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 ;;
}
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
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
@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 ;;
}
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
@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
;;
}
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
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
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
@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})