Oracle BI EE 10.1.3.3./2 – Understanding Todate and AGO – Achieving YTD, QTD and MTD

Though i had known about the existence of repository functions Ago and TODate, i had never used them before especially in the context of achieving YTD, QTD and MTD. So, i thought i would give it a try and see how it works and of course find out the possible use cases of using these functions. In this article we would see how to go about using ToDate function. Just remember that we do not have these functions in Answers. To use these one would have to create custom logical columns in the repository. Before we start lets look at the syntax of both of these functions.

TODATE:

      TODATE(<Measure Column>, <Level Column in the Time Dimension>)

      Eg.   TODATE(BISE1_TUTORIALWH.SALES.AMOUNT, BISE1_TUTORIALWH.TIMESDim.”Year”)

AGO:

      AGO(<Measure Column>, <Level Column in the Time Dimension>, <Shift Required>)

      Eg:    AGO(BISE1_TUTORIALWH.SALES.AMOUNT, BISE1_TUTORIALWH.TIMESDim.”Month”, 1)

Now lets look at TODATE function in particular. According to the docs TODATE function helps in achieving Year to Date(YTD), Month to Date(MTD) and Quarter to Date(QTD) kind of functionality. So what does YTD, QTD and MTD mean in the context of say Sales(a measure). Lets understand these first.

YTD Sale   –   It means the summation of sales from 1st Jan of the current year to the Current Date. The current Date is kind of a misnomer since its value can vary(can have values like day, month, quarter etc) depending on the granularity. We will understand this while going over these functions in detail.

QTD Sale   –   It means the summation of sales from first of first month of the current quarter of the current year to the Current Date.

MTD Sale   –   It means the summation of sales from first of the current month to the Current Date.

The base schema that i use to demonstrate for this will be the BISE1_TUTORIALWH that gets bundled with bise1. The first step that i did was to create a time Dimension. TODATE and AGO can be used only along with Time Dimensions. Lets create one with the following hierarchy.

      Year   ->   Quarter   ->   Month of Year   -> Dimension Key

      

Remeber while creating the Dimension mark it as a Time Dimension.

      

Also, the lowest unique level has to be identified as the chronological key for the time dimension. In our case Dimension Key would be the chronological key.

      

Now lets create a new Logical column YTD Sales. As you see below, TODATE will take sales as one parameter and a level in the Time Dimension(not the logical table) as another parameter. Similarly, lets create QTD Sale and MTD Sale.

            

      

Remember that these custom logical columns are very specific regarding their granularity. Varying the granularity will give wrong results in the answes. Lets understand this by creating a simple report. Our report will have the following columns

   Year   Quarter   Month   Sales   YTD sales

      

Lets look at the results and try to understand what this function does.

      

As you see above YTD Sales basically achieves a rolling sum. For example, If you take Year 2003, Quarter 1 and Month 2, the sales is 20,095. But the YTD Sale is a summation of Month 1 + Month 2 = 20095 + 3683 = 23778. We do not have a day level granularity. We only have a month level grain in the time dimension hence, it rolls it up to the month level. Now lets remove the Month of year column and just have Year, Quarter, Sales and YTD Sales.

      

      

Oops. What has happened to the YTD? According to the definition of YTD, for Year 2003 and Quarter 1, we must have a summation of sales for all the months in Quarter 1. i.e. 3683 + 20095 + 4157 = 27936. But what we have in the actual result is 55397. How is that possible? Thats one of the reasons why understanding grain in TODATE and AGO is very important. As we removed the month from the report, what YTD has done is it has done a summation of the YTD values of all the months in the previous report instead of the Sales ie. 3683 + 23778 + 27936 = 55397. Same would be the case for MTD and QTD.

      

      

Here we have seen only TODATE. Lets looks at AGO in a later article.

Advertisements