Define YTD(Year to date) logic on Query Designer

[Former Member]

Bex queries with YTD(Year to Date), month average calculation etc. are usually used in analyzing the metrics, input to dashboards etc.

There are various ways of setting up the YTD. In this case, we will see how to set-up a YTD logic on the fly on the query designer.

To perform a YTD calculation for a keyfigure, you should have Calmonth, Calyear on your "selection or RKF". This selection should be called in a "formula or CKF" with standard aggregation and reference characteristic as "Calmonth".

Some of the key steps on how to set-up the YTD calculation :

1. The Calmonth on your “Selection or RKF” : this should be always <= (less than or equal to). What this signifies is consider all the months for calculation of YTD.

.

   2.  Make sure you also have a CALYEAR in your “Selection or RKF”, this should be derived based on the calmonth that you input as a variable screen or customer exit. You have to calculate or find the year via a routine or by using the offset functionality on the variable properties .. i.e, offset =4, length =4.. (as shown below )

3.  Based on the steps 1 and 2, we can define the "Selection or RKF" as shown below :

Please note, In the aggregation tab there is no change, use the standard exception aggregation. Since this is a "selection or RKF" we don't use the reference characteristic here.. Instead we will use in the next step- 4.

.

    4. Based on the step 3 -"selection or RKF", We define the "formula or CKF" , with the aggregation as standard aggregation and reference characteristic as “0CALMONTH”..

Hence, these simple steps will allow us to define the YTD calculation for a specific key-figure. You can also calculate  last year YTD by setting the offset on the calender year.