Prior YTD, MTD, etc


(Ben Silverstein) #1

Hi there,

Forgive the rookie question (I’ve been a Looker user for about a week now) but I’m trying to make a Prior YTD Measure (summing values from the start of the previous calendar year until today’s day in the previous calendar year) and am getting strange results.

  • I started with a number-type dimension called sales
  • I also have a time-type dimension group called posting_date to track sales dates
  • From there, I’ve created a sum-type measure called sales_pytd
  • I have two filters applied to sales_pytd: posting_date value = “before 365 days”, and posting_date value = “last year”

In practice, what I’m seeing is the “before 365 days” seems to be ignored, and the only filter which applies is “last year”. Any help on this would be appreciated.

As an aside, thanks to anyone who’s been posting here - I have already learned a ton from you all.

Cheers,
Ben


(Andrew Powell) #2

Hi Ben,

Thanks for posting! It would be super useful if you could add some sample code to your question, that makes the problem a lot easier to debug.

Thanks
Andy


(Ben Silverstein) #3

Thanks for responding, Andrew!

Here’s my code below:

measure: sales_pytd {
description: “sales prior year”
type: sum
sql: ${sales};;
filters: {
field: posting_date
value: “last year”
}
filters: {
field: posting_date
value: “before 364 days ago”
}
value_format_name: usd_0
label: “Sales ($), Last Year”
view_label: “Sales ($)”
}


(Nick Morrison) #4

Hi Ben,

In my data model, I created a YTD filter that can be applied to results, rather than a measure. Based on the the timeframe you use, it will determine the YTD result for the current year and any previous years.

dimension: is_before_ytd {
label: "Is YTD?"
type: yesno
sql: 
(EXTRACT(MONTH FROM ${TABLE}.posting_date) < EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
OR
(EXTRACT(MONTH FROM ${TABLE}.posting_date) <= EXTRACT(MONTH FROM CURRENT_TIMESTAMP) 
AND
EXTRACT(DAY FROM ${TABLE}.posting_date) < EXTRACT(DAY FROM CURRENT_TIMESTAMP))
OR
(EXTRACT(MONTH FROM ${TABLE}.posting_date) <= EXTRACT(MONTH FROM CURRENT_TIMESTAMP) 
AND
EXTRACT(DAY FROM ${TABLE}.posting_date) <= EXTRACT(DAY FROM CURRENT_TIMESTAMP) AND
EXTRACT(HOUR FROM ${TABLE}.posting_date) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
OR
(EXTRACT(MONTH FROM ${TABLE}.posting_date) <= EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AND
            EXTRACT(DAY FROM ${TABLE}.posting_date) <= EXTRACT(DAY FROM CURRENT_TIMESTAMP) AND
            EXTRACT(HOUR FROM ${TABLE}.posting_date) <= EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AND
            EXTRACT(MINUTE FROM ${TABLE}.posting_date) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)
          ) ) ;; }

(Ben Silverstein) #5

Nick - thanks so much for this! I’m going to have to review the code a little bit, as I’m getting an Incorrect Syntax on keyword ‘FROM’ on my environment. Just a question, with the disclaimer that again, I’m new to SQL. Where you wrote:

(EXTRACT(MONTH FROM ${TABLE}.posting_date) <= EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
would making that an = instead of <= to reduce your query size (since the previous < statement covers that) make a performance difference in your opinion?


(Nick Morrison) #6

Hi Ben,

Regarding the ‘<=’ part, this is used to make the YTD logic work correctly at different timescales, depending on which posting_date timeframe is used in the analysis.

If you have run the query by grouping everything under a Month timeframe, the YTD measurement would need to use ‘<’ as the current month has not finished e.g. analysing whole month results as YTD during February should only include January as it was the last complete month.

If the query was run at a minute timeframe, then the LookML would reference the OR statement that specified the Minute timescale. This code uses ‘<’ for the minute, but ‘<=’ for everything else as the hour / day / month could still be ‘=’ in a YTD measurement.

I’m not sure of the performance impact, but it is important from a logic standpoint to to use ‘<=’.

I hope that all made sense!

PS. You might want to take a look at the LookML course offered in Looker Training for some ideas as to how you might customise the code for your specific situation (e.g. the use of the FROM ${TABLE} part).


(Ben Silverstein) #7

That made perfect sense - I totally forgot about the ramifications of filtering on other timeframes, and was thinking about standard datetime fields!

My error in thinking was actually an embarrassingly basic one. Having only ever used MS SQL 2008 and being self-taught - I didn’t think about the dialect (or how I’d never seen MS SQL 2008 code structured like that). Once I re-formatted it for our servers, this worked like a dream. Thanks again for your help, Nick!