YTD LY comparison


(Christine Kelly) #1

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


(lloyd tabb) #2

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.


(Christine Kelly) #3

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.


(Jon Brenner) #4

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 :slight_smile:


(Christine Kelly) #5

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


(lloyd tabb) #6

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


(Christine Kelly) #7

@lloydtabb Thank you :slight_smile: 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