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.
Hi Beto,
There are two ways you can do this, to dynamically pick the date.
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 ;;
}
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
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,
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.
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:
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 ;;
}
It seems the second option would be easier for you, and gives you the table you require
Hope this helps
Naomi