Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

YTD LY comparison

  • 27 July 2018
  • 6 replies
  • 637 views
C

Hello,


I might have missed something but besides using the matches to filter, “2018,2017” I do not see how you can compare to previous year easily. I get the full year of 2017 and YTD 2018 which are not comparable. I would need to be very specific entering exact dates.


What would be the most efficient way to compare YTD N to YTD -N ?

I found a thread on how to implement the YTD but I am stuck on how to apply it to a LY analysis?



Thanks

Christine

6 replies

lloydtabb
Rank
Userlevel 6
Badge

There are some features that are coming that are going to make this easier, but here is an interesting hack that lets you do all kinds of comparisons of year to date, month to date, etc.


Add a dimension for the current time (below is code for redshift). Omitting the timeframe parameter gives me all the possible time formats.


  dimension_group: current {

    type: time

    sql: GETDATE() ;;

  }

You can then use a custom filter to the times you are looking for. For example, I’m going to filter for the past 2 years but only days before today. You could do this with current week, month, etc



Add the time frame and pivot. You can see n the results below, we only have the months we want.



And finally, our year to date comparison.


C

thanks for the help!!! We will find a way to use it to postgresql:)

also figuring out how it can be applied to a dynamic filter, if you have any hacks on that too let me know:)


glad to hear there are improvements for YTD LY analysis.

J

My team got around this by creating additional dimensions that use MM-YYYY as strings rather than dates. I don’t love it but it got the job done 🙂

C

@lloydtabb hello, I was just wondering if I may ask if you have any updates on the comparisons ? Thank you!

lloydtabb
Rank
Userlevel 6
Badge

@Christine_Kelly yes. You can create custom measures that are filter by a timeframe. Its still early, but you can start to play with it.



C

@lloydtabb Thank you 🙂 We already had this feature tested. Even if I can see how it will benefit other users it is likely not very useful to us for time comparisons. Because we already have access to modify LookML.

I might have missed something but we my understanding is that the objective is to make ad hoc analysis easier and flexible to use by bypassing the LookML.


Would you know of any other updates?


Thanks in advance

Christine

Reply


Terms of Service