Filter on this month but also see previous month's data

Knowledge Drop

Last tested: May 2020

The Problem

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?

The Solution

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.                

Comments
Beto
Participant II

Im trying to implement sort of a period over period logic in which:

  • first i have a dimension that calculates which year i am in and then substracts one so i get the previous year.
  • once i have the previous year i want to filter a measure based on thar value but then there is no option to filter based on a dimension, you have to pass a string argument as a value, like why is there no option to filter based on a dimension , its the most natural thing ever !!!!
  • then because i have no option to filter on that dimension i opted to do a case when , the condition being the year of my date dimension superior to the dimension that gets the previous. My issue is that it is still adding all of the data instead of just those cases that fit the condition.

Any ideas on how to solve this ? I’m trying to move away from derived tables 

sam8
Staff

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"]
}
Beto
Participant II

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:

  • last_year = 2020
  • mydate = 2021
  • so when 2021 -1 = 2020 then
  • add all in sales

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.

yadderace
Participant I

Hi @Beto , I implemented something like you need. This was my result:

b256222e-2b8e-41c7-b9fe-3bb67fa62287.png

I used this article and I implemented the 8th method.

Let me know this is what you need.

Sena
New Member

Hi @Beto , I implemented something like you need. This was my result:

b256222e-2b8e-41c7-b9fe-3bb67fa62287.png

I used this article and I implemented the 8th method.

Let me know this is what you need.

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?

Version history
Last update:
‎04-05-2021 09:09 AM
Updated by: