Blogs / SAP / SAP BusinessObjects / SAP Lumira Discovery / YTD, QTD, MTD Calculations in SAP Lumira Discovery

YTD, QTD, MTD Calculations in SAP Lumira Discovery

Mar 2, 2018

SHARE

This is part of the SAP Lumira Discovery blog series.

According to Wikipedia, Year-to-date (YTD) is a period, starting from the beginning of the current year (either the calendar year or fiscal year) and continuing up to the present day. YTD is used in many contexts, mainly for recording results of an activity in the time between a date (exclusive, since this day may not yet be “complete”) and the beginning of the year.

In SAP Lumira Discovery, we would need to leverage the Calculations function to build a custom dimension for this purpose. The basic requirements for performing this calculation is,

– A date field against which you would want to compare
– A measure that needs to be basis for this calculation
– Year() function

YTD, QTD, MTD Calculations in SAP Lumira Discovery

 
– CurrentDate() function

YTD, QTD, MTD Calculations in SAP Lumira Discovery

– if-then-else statement

YTD, QTD, MTD Calculations in SAP Lumira Discovery

We can then calculate the YTD custom dimension using the formula below,

YTD, QTD, MTD Calculations in SAP Lumira Discovery

We can then extend the same concept to building QTD (Quarter-to-Date) and MTD (Month-to-Date) calculations as shown below,

YTD:
if Year({Date})=Year(CurrentDate()) and {Date} <=(CurrentDate()) then {Sales} else 0
QTD:
if Quarter({Date}) = Quarter(CurrentDate()) and {Date} <= (CurrentDate()) then {Sales} else 0
MTD:
if Month({Date}) = Month(CurrentDate()) and {Date} <= (CurrentDate()) then {Sales} else 0

This can be even extended to previous duration values as shown below,

PYTD:
if Year({Date}) = Year(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0
PQTD:
if Quarter({Date}) = Quarter(CurrentDate())-1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0
PMTD:
if Month({Date}) = Month(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0

An important approach would be to create a custom dimension first and then create a measure. This process would work only for imported data, as these measures can be directly inherited from SAP sources via a live connection.

Get your business users trained on more best practices with calculations in SAP Lumira Discovery.

Want to know more? Click here to get in touch.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!