create dynamic measures with parameter selcted date by user

tatuspark
Participant IV

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 Solved
0 7 624
1 ACCEPTED SOLUTION

@tatuspark I had a similar requirement recently and found the solution in this article: https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures- 

View solution in original post

7 REPLIES 7

Hi tatuspark,

It sounds like you’re trying to create a period comparison. You can do this with the “Mathces (Advanced) filter in the Explore window:

last month, 13 months ago

Also, I’d suggest renaming your dimension “vente” to “vente_id” and your dimension “vente_date” to “vente”, as Looker will automatically append “_date” to the name in the Explore window, so you’ll get “Vente Date Date” or “Vente Date Moin” (I don’t know if Looker translates to French, if not, it’ll say “Vente Date Month”).

Many thanks.

tatuspark
Participant IV

Hi Cory

Thank you for your answer.

I want to compare for example October 2020 vs October 2019. And I want to create a dynamic filter that allows the user to choose the month / year they want to compare to the same month in the past year. Thank you

Hi tatuspark,

It sounds like you’re trying to create a period comparison. You can do this with the “Mathces (Advanced) filter in the Explore window:

last month, 13 months ago

Also, I’d suggest renaming your dimension “vente” to “vente_id” and your dimension “vente_date” to “vente”, as Looker will automatically append “_date” to the name in the Explore window, so you’ll get “Vente Date Date” or “Vente Date Moin” (I don’t know if Looker translates to French, if not, it’ll say “Vente Date Month”).

Many thanks.

@tatuspark I had a similar requirement recently and found the solution in this article: https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures- 

tatuspark
Participant IV

@tatuspark I had a similar requirement recently and found the solution in this article: https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures- 

@jarguelles  Please if I want to calculate the measure for the last month date of the date chosen by the user (date_filter). how to write the syntax

tatuspark
Participant IV

@tatuspark I had a similar requirement recently and found the solution in this article: https://help.looker.com/hc/en-us/articles/360001286007-Creating-Filtered-Measures- 

@jarguelles  Please if I want to calculate the measure for the last month date of the date chosen by the user (date_filter). how to write the syntax

@jarguelles  

yes that solves part of my problem with this solution I calculate the sum in month N or year N chosen by the user in the filter. Now if I want to calculate the sum of the year N-1 and the month N-1 of the date chosen by the user in the filter how will I do it?

@tatuspark I think you’ll need to do a separate date dimensions that gives you the “1 month ago” and  “13 months ago”, then use those dimensions to create their separate “satisfies filter” dimension (using the same date filter in each), and then use those satisfies filters for their separate measures. Now the three measures can coexist and use the same filter.

tatuspark
Participant IV

@tatuspark Je pense que vous devrez créer une dimension de date distincte qui vous donne le "il y a 1 mois" et "il y a 13 mois", puis utiliser ces dimensions pour créer leur dimension "filtre satisfaisant" distincte (en utilisant le même filtre de date dans chacune d'elles) , puis utilisez ces filtres de satisfaction pour leurs mesures distinctes. Désormais, les trois mesures peuvent coexister et utiliser le même filtre.

@jarguelles please can you put me the code to create these dimensions. thank you

Top Labels in this Space
Top Solution Authors