Last tested: May 2020
You want to give your users a single date selector, where they can choose a month. Then, when they select a month, they should see data from that month and the previous month.
Filtering for this month on a date dimension will remove any data from the past month, unfortunately. How to show both?
Assume ${date_raw}
is the date you want to filter on. (Make sure to use the raw
timeframe if it's a dimension group!)
First, create a filter
field for the user to interact with. Remember that a filter
field doesn't immediately update the WHERE
clause, but can be targeted with Liquid.
filter: date_selector {
type: date
}
Second, create a yesno
dimension that captures the value of date_selector
using liquid. This will be your "this month" indicator.
dimension: is_selected_month {
type: yesno
sql: {% condition date_selector %} ${date_raw} {% endcondition %} ;;
}
Third, create a yesno
dimension that captures the start and end of date_selector
and subtracts a month. The SQL will vary by dialect.
dimension: is_previous_month {
type: yesno
sql: ${date_raw} > ADD_MONTHS(-1, {% date_start date_selector %}) AND ${date_raw} < ADD_MONTHS(-1, {% date_end date_selector %}) ;;
Fourth, put these yesno's into filtered measures as desired.
measure: count_selected_month {
type: count
filters: {
field: is_selected_month
value: "yes"
}
}
measure: count_previous_month {
type: count
filters: {
field: is_previous_month
value: "yes"
}
}
This content is subject to limited support.
Im trying to implement sort of a period over period logic in which:
Any ideas on how to solve this ? I’m trying to move away from derived tables
Hey @Beto ! I have some ideas here.
Option 1: Do you need a secondary dimension to calculate “previous year”? It looks to me like you could make a filtered measure where the date is filtered on last year using date filter expressions. Something like:
measure: last_year_count {
type: count
filters: [created_date: "last year"]
}
Option 2: Let’s say you do need a secondary dimension which calculates the previous year as you mentioned. Then, it is possible to filter on a dimension - the workaround I usually use is to make a yesno dimension with my desired condition, and filter the measure for that dimension to be “yes”. Something like:
dimension: is_last_year {
# This dimension assumes that the dimension "last_year" has already been computed.
type: yesno
sql: EXTRACT_YEAR(${created_date}) = ${last_year} ;;
# The SQL to use for "EXTRACT_YEAR" may differ depending on your database.
}
measure: last_year_count {
type: count
filters: [is_last_year: "yes"]
}
I think i replicated your idea in a different way in which i have the following:
dimension: last_year{type: string sql: extract(year from CURRENT_DATE())-2 ;;}
then i have this
measure: last_year_sales{
type: sum
sql:
CASE
WHEN (EXTRACT(YEAR FROM ${MYDATE_date})-1) = ${last_year}
THEN ${sales}
END;;
value_format_name: decimal_0
}
in that way , i get:
the issue there is that is adding all and returning the same value as if i were adding 2021 , what i need is to only add sales for 2020 and then display those values in a table at the same level as 2021 because i want to make a comparison.
Month | Sales 2020 | Sales 2021 |
---|---|---|
January | 100 | 110 |
February | 110 | 110 |
that is what i want to achieve , like i said before i’ve done it using derived tables but im trying to move away from that solution and i thought i could do it this way.
Hi, can you explain your way? And also i need to dynamic time period. For examle, if the users choose time selection month/months/querter/year/month of year/ just period (10 days/2days ex.).
Have you got any idea about it?