Extracting Month and year based on current selection

I want to create comparison chart like it should pull most recent month of year and compare it same month of previous year.
Eg My recent month is Aug 2017 it should pull the same and compare it with Aug 2016 (it should display bar graph of Aug 17 and Aug 16)

0 10 11.1K
10 REPLIES 10

You can use custom filters here, @Swapnil_Pimple.

An example:

(extract_months(${orders.created_month}) = extract_months(now())-1)
AND
(extract_years(${orders.created_date}) = (extract_years(now()))
  OR extract_years(${orders.created_date}) = (extract_years(now())-1))

Afreen_Peje
Participant II

hey thanks, I am getting only one row

Please help me to get row of current month

working 🙂
(extract_Quarter(${orders.created_Quarter}) = extract_Quarter(now())-1)
AND
(extract_years(${orders.created_date}) = (extract_years(now()))
OR extract_years(${orders.created_date}) = (extract_years(now())-1))

I used this logic for Quarter comparison for eg it shows result for recent quarter vs same number of quarter of previous year (Q3 2017 vs Q3 2016)
is this right?

extract_Quarter isn’t a valid function here, you’ll want to use the extract_months() instead. Something along the lines of this example:

(extract_months(${orders.created_month}) = extract_months(now())-1)
OR (extract_months(${orders.created_month}) = extract_months(now())-2)
OR (extract_months(${orders.created_month}) = extract_months(now())-3)

thanks its working now

Harika1
Participant I

Hi…

In Single value chart can we display present month value vs previous month value using “Comparison” option?

Regards,
Harika.

There’s a good approach that’s described in @Harika1

Instead of selecting the current date, is it possible for the user to select the date (make it a dynamic filter) and using the selected date as a reference, pull the same month of the previous year?

There’s a few ways you can have this same functionality, though they all require using LookML. In general, you’ll create a custom filter or parameter which will allow the user to select a month and then you’ll use liquid syntax to apply that as a filter.

As an example, I created the parameter and a yesno dimension. We can then filter on the yesno dimension in the explore:

parameter: month_selector {
type: date
label: "Month to Filter On"
description: "Use this field to select a month to filter on"
 dimension: is_selected_month {
    type: yesno
    sql: 
        CASE
          WHEN extract(month from {% parameter month_selector %}) = extract(month from ${created_raw})
            AND 
              (extract(year from {% parameter month_selector %}) = extract(year from ${created_raw})
              OR extract(year from {% parameter month_selector %}) = extract(year from DATEADD('yr', 1, ${created_raw}) )
              )
            THEN TRUE
          ELSE FALSE
        END ;;
  }

Here’s a really good article which goes more in-depth: [Analytic Block] Flexible Period-over-Period Analysis. You can take it a step further and allow users to select a date, which granularity they’d like to compare (months, quarters, etc).

Found the above example as well as this Custom filter Post useful for a case that needed to filter on a single month in the dashboard and the tiles are filtered for the desired month and one month back.

parameter: month_selector {
  type: date
  label: "Month to Filter On"
description: "Use this field to select a month to filter on"
}

 dimension: is_this_&_last_month {
    type: yesno
    sql: 
        CASE
          WHEN extract(month from {% parameter month_selector %}) = extract(month from ${created_raw})
            AND 
              (extract(year from {% parameter month_selector %}) = extract(year from ${created_raw})
              OR 
            extract(month from {% parameter month_selector %}) = extract(month from DATEADD(month, -1, ${created_raw}) )
            AND 
              (extract(year from {% parameter month_selector %}) = extract(year from ${created_raw})
            THEN TRUE
          ELSE FALSE
        END ;;
  }

The case statement optional as the comparisons would evaluate to true or false either way. Also the extract and Dateadd functions/syntax will depend on your sql dialect but the concept is the same (Current month or last month).

Top Labels in this Space
Top Solution Authors