Question

How to calculate YTD for every date period in history and not just as now?

  • 25 November 2021
  • 2 replies
  • 11 views

Hi

I am trying to replicate existing reports which include MTD and YTD measures. I don’t want the YTD as per now but for each and every period in the past.

For example let’s say we are reporting the number of sales by week (from a sales daily model):

Week | Sales

1  | 2234

2 | 3443

3 | 2653

etc.

What I need now is also the running YTD for each week like:

Week | Sales | Sales_YTD

1  | 2234 | 2234

2 | 3443 | 5677

3 | 2653 | 8330

I tried joining my sales view in to another sales view but didn’t succeed, I thought maybe derived tables? I’ve seen many posts about YTD but none really covers what I need.

In summary: I should be able to select one date dimension (day, week, month) and then have two measures: Sales and Sales YTD (Sales YTD would be the sum of sales from the 1st Jan to the end of the period selected in the date dimension)

It’s like a left join from the sales view in to another derived sales YTD view where the sale date would be between 1st Jan and the first view’s dimension end date).

I hope this is clear and that there is a genius out there with a creative solution.

Thanks


2 replies

Userlevel 4

@jaime.serafim-1633423242 Have you tried using a running_total measure type?

https://docs.looker.com/reference/field-reference/measure-type-reference#running_total

 

@shiggins No I didn’t. I am quite new to lookML. This looks very interesting, I assume there would be a way to do the running total by year (or by month), so I wouldn’t  count 2014 sales in 2015?

Thank You

Reply