YoY growth for a date range specified in filter

(Rashmi) #1

I have been able to show top countries with most views for the given date range like below

I now would like to show how the total views has changed for those countries YoY. ie. if my selected dates are between 2015-01-01 to 2015-04-01 ( first quarter of 2015). I want to see the % change in the total views for those countries compared to the first quarter of 2014 ( one year before selected date range).

I know that you could use pivot_offset to compare YoY just based on the year like suggested in Using offset and pivot_offset in table calculations (3.18+) . but how to restrict so that my YoY comparison takes into account the values set in the date filter ? As far as I see we cannot access the values supplied in filter in the calculated fields? Please advice if there is a way of doing this.

1 Like

#2

I haven’t tested it, but I think something along the lines of the following should work —

- dimension_group: ds_with_yoy
  type: time
  sql: |
    ( {% condition ds %} (${TABLE}.ds + interval 1 year) {% endcondition %}
    or {% condition ds %} ${TABLE}.ds {% endcondition %} )
  can_filter: false

— if you filter on ds_with_yoy in the first quarter of 2015, include ds in your query, and pivot on year. Good luck, and let us all know how it goes.


The + interval notation is MySQL; your dialect may have something different to add an interval to a datetime.

0 Likes

(jake) #3

Hi Rashmi.

The trick here is that you want to filter on dates within each year but across multiple years. This is a great use case for timeframes. So if you add a timeframe like quarter_of_year to the dimension_group ds you can filter on that and the timeframe in which you are interested.

If you want more granular control over the intra-year time you could use timeframes like month_num or day_of_year.

1 Like

(Rashmi) #4

Thanks Michael !
I don’t know if I properly understood your proposed solution. But as I understand this won’t work because when we apply the filter and select say first 2 months of 2015, then that will restrict the output of the query to only those 2 months of the year 2015 no matter what you have in the dimension or dimension_group definition.
Also, when you write templated filters, you always specify sql_or_lookml_reference inside the condition. So I don’t think you can specify a sql expression like (${TABLE}.ds + interval 1 year) within it.
Please let me know if I misunderstood your suggestion.

Thanks again!

0 Likes