MTD, YTD

[felixmw]

First of all, thank you for providing this awesome tool. I have just started to play around with Turnilo and I am pretty impressed by its set of features and its ease of use.

One question that I am coping with at the moment is if it is possible to generate measures in Turnilo that calculate a "month to date" or "year to date" for a specific measure column, e.g. revenues? I guess Plywood is the route to take but I have not been able to come up with a solution yet.

Thanks for your support, Best Felix

[adrianmroz]

Sorry, I don’t understand. You want to see difference between measure value now and month earlier? That is Time shift setting in Time filter menu. Or something else?

[felixmw]

Hi Adrian,

and again thanks a lot for your timely response.

I am looking for something similar to the PeriodsToDate() Function in MDX (e.g. https://docs.microsoft.com/de-de/sql/mdx/periodstodate-mdx?view=sql-server-ver15). As everything in MDX the explanation for this function is a bit hard to grasp. What it basically does: it returns a set of values along a time dimension starting from the beginning of a specific period to the given date. So if I am interested in the month to date (MTD) value for the KPI "revenue" for the date 15.10.2019 I am interested in the sum of all revenues generated between 01.01.2019 and 15.10.2019. It is the accumulation of the revenues from the beginning of the month up to a given date. Some holds for year to date (YTD) or week to date (WTD). Does this make sense?

Best Felix

[adrianmroz]

I think I get what this function does. For single value it’s simple. In turnilo you would just select proper time period. I can’t imagine how such function should work for longer periods. What would you expect when I select whole year in time filter, set somewhere date to 15 Oct and select MonthToDate? It should bucket each month from 15th to 15th and show sum for this bucket?

[felixmw]

A common use case where MTD or YTD KPIs are used is to compare revenue values and their cumulative development against budgets or numbers from the previous year/period. E.g. you would graph MTD numbers for one month on a daily granularity as line charts in order to see where cumulative revenues get above budget or below the performance of the same month of the previous year. The same could be done for an entire year (or fiscal year). So you normally choose a time period of one month for MTD KPIs or one year for YTD KPIs. Let's take September 2019 as the time period with day granularity. So you get 30 data points - one per day. For 01.09.2019 you get the revenues generated on that day. The data point related to 02.09.2019 are the revenues of 01.09.2019 plus all revenues generated on the second day of September...and so on and so forth. So in general - and if your business is running well - you get a rising line graph from these numbers that tell you how much revenues got accumulated over the different days of a month.

Another handy use case is to get the value of different accounts of your balance sheet. If your data hold all transactions on your accounts you get the actual balance of a specific account for a specific day by choosing the YTD value for that day including the opening entry.

Talking about how to integrate this into Turnilo, the best I can think of is to add to the pop up of the time filter. Apart from choosing a fixed or relative period and a time shift it would be great to choose whether the data should be atomic or cumulative for different periods (year, quarter, month, week, day, ...). What periods the user can choose from could also be configured similar to the "granularities" attribute that exists for the relative period filter.

Best Felix

[adrianmroz]

Let's go with points:

1. I understand use case and I find it useful, so thank you.
2. Unfortunately I don't know how to implement it inside plywood query. That needs some research spike task.
3. I think it shouldn't be option of time filter but some kind of advanced measure. It wouldn't work with measures that are not simple long-sums (or counts) so adding to time filter would lead to a lot of suprises. But that's discussion that we should have after getting any kind of proof of concept for plywood query.
4. We will discuss internally if that's feature we want to focus on and with what priority.

[mkuthan]

Functions like YTD/MTD should be provided on database level (Druid). This is the only way to achieve efficient and correct implementation.

[felixmw]

@adrianmroz : Thanks for your feedback and I agree that this should be handled as a separate measure. This is how it is handled in MDX as well.

@mkuthan : Agree that this would be an optimal solution; but an intermediate solution in between would help as well. Do you know if there are any plans to integrate such a functionality in Druid? Since it is a timeseries / OLAP database and since PeriodeToDate is a common functionality of MDX it should not be that far from likely, right?

Best Felix

[mkuthan]

@mkuthan : Agree that this would be an optimal solution; but an intermediate solution in between would help as well. Do you know if there are any plans to integrate such a functionality in Druid? Since it is a timeseries / OLAP database and since PeriodeToDate is a common functionality of MDX it should not be that far from likely, right?

After quick research I found old question: https://github.com/apache/incubator-druid/issues/6434

You could also check moving average query extension but I do not expect any support from Plywood (library used by Turnilo for communication with Druid). https://druid.apache.org/docs/latest/development/extensions-contrib/moving-average-query.html