Extracting Month and year based on current selection

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

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?

thanks its working now

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

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