Solved

# Dynamic measure with date selected by the user

• 15 replies
• 1312 views

Userlevel 1
• Member
• 29 replies

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 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})

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 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

@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

@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  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 1

@tatuspark

``  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 2

@tatuspark

``  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

@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 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 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

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.

Expand Occurrences

2 occurrences

tf_vente.view:754 universite_poc:tf_vente

tf_vente.view:759 univeriste_poc:tf_vente

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
`@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,

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