Extracting Month and year based on current selection

done
normal_priority
reply

(Swapnil Pimple) #1

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)


(Brecht Vermeire) #2

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) #3

hey thanks, I am getting only one row

Please help me to get row of current month


(Swapnil Pimple) #4

working :slight_smile:
(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?


(Brecht Vermeire) #5

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)

(Swapnil Pimple) #6

thanks its working now


#7

Hi…

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

Regards,
Harika.


(Brecht Vermeire) #8

There’s a good approach that’s described in this article, @Harika


(Jessica) #9

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?


(drake) #11

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).