Looker date filter for last year

Beto
Participant II

Hello everyone,

I’m new at looker and while doing some operations i found myself in a positiion where I need to show a comparison between “This year” and “Last Year” sales.

At first I did the following:

measure: ventas_netasAA {
label: "Venta NetaAA"
type: sum
sql: ${venta_neta} ;;
filters: [fecha_year: "last year"]
value_format_name: usd
}

My issue then is that it appears to only work on this “2021” year vs the “2020” but the rest of the data (2019, 2018) is shown as zero. 

My logic is , if the year im trying to compare is 2021 i wanna see past years so switch to “year -1” and then display the aggregate, the issue is that i am not sure on how to do that on LookML.

0 5 3,302
5 REPLIES 5

Naomi_Johnson
Participant III

Hi Beto, 

There are two ways you can do this, to dynamically pick the date.

  1. You can use a parameter, which is a filter-only field, which allows you or another user to dynamically choose the year when Exploring the data
parameter: select_year {
type: number
default_value: "2021"
}

measure: sale_in_selected_year {
type: sum
sql: case when ${created_year} = {% parameter select_year %} then ${sale} end ;;
}

measure: sale_in_previous_year {
type: sum
sql: case when ${created_year} = {% parameter select_year %}-1 then ${sale} end ;;
}
  1. If you wanted to have more choice on the exact days you want to look at (e.g. last 7 days vs the same days last year), you can choose a templated filter. This is also a filter-only field which allows the user to dynamically choose the dates in the Explore
filter: select_date {
type: date
}

measure: sale_in_selected_year {
type: sum
sql: case when {% condition select_date %} ${created_raw} {% endcondition %} then ${sale} end ;;
}

measure: sale_in_previous_year {
type: sum
sql: case when {% condition select_date %} dateadd(year,1,${created_raw}) {% endcondition %} then ${sale} end ;;
}

Hope this helps!

Naomi

Beto
Participant II

Thanks Naomi,

I tried to implement your solution but it didn’t work for me. I’m not sure if I did somehting wrong, the results were the same like I was not selecting different years.

Furthermore the comparison was not easy becuase instead of being on the same row level the years were phased. Is there a way in which I can get something like the following example?

Shop Year 2019 Year 2020
Shop 1 $ 105,000.00 $ 115,000.00
Shop 2 $ 250,000.00 $ 240,000.00
Shop 3 $ 305,000.00 $ 325,000.00
Shop 4 $ 450,000.00 $ 430,000.00

In that example , I want my filter to show me the years based on what I select, by default it would showme 2020 vs 2021 , but if i select 2020 then it would be as in the example above.

Thanks for your help,

Beto
Participant II

Hi Naomi,

I implemented your solution but it didn’t work, probably because I did something wrong being my first time using liquid and being very fresh at Looker.

I want to achieve something like this:

Month Year 2019 Year 2020
January $ 10.50 $ 11.50
February $ 15.00 $ 14.00
March $ 22.50 $ 22.00

When I implemented your solution my result was not different, showing the same data for both years.

I appreciate your help with this topic.

Sorry @Beto , my previous post doesnt answer your question after I read it again.

Naomi_Johnson
Participant III

Hi Beto,

No worries, liquid can be tricky the first few times.

There are two ways you can have selected year and previous year, by month. I suggest implementing my second suggestion, as it is simpler and requires no liquid:

  1. Using the code in the first example I gave, and adding ‘month_name’ to the sale date dimension_group.

This allows you to select Sale Month Name in the results, and choose the year 2020 with select_year parameter

dimension_group: created {
type: time
sql: ${TABLE}.created_at ;;
timeframes: [raw, date, week, month, year, month_name]
}

parameter: select_year {
type: number
default_value: "2021"
}

measure: sale_in_selected_year {
type: sum
sql: case when ${created_year} = {% parameter select_year %} then ${sale} end ;;
}

measure: sale_in_previous_year {
type: sum
sql: case when ${created_year} = {% parameter select_year %}-1 then ${sale} end ;;
}
  1. This suggestion doesn’t need any advanced measures or liquid:
    1. Add month_name as a timeframe to your sale dimension_group
    2. In your explore, choose Sale Month Name, pivot by Sale Year and choose your normal sale measure
    3. Filter Sale Date between 2019-01-01 and 2021-01-01

It seems the second option would be easier for you, and gives you the table you require

Hope this helps

Naomi

Top Labels in this Space
Top Solution Authors