Question

Extracting Month and year based on current selection

  • 22 September 2017
  • 10 replies
  • 8468 views

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)


10 replies

Userlevel 4
Badge

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



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?

Userlevel 4
Badge

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

Hi…



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



Regards,


Harika.

Userlevel 4
Badge

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





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

Reply