Skip to Content

How to calculate YTD,MTD and QTD values

Hi folks,

I am beginner in BO.I am using BO 4.2 and have a scenario to create YTD ,MTD and QTD values for particular customer based on date given in prompt by creating variables . In the Query filter I have created filter on date to fetch the data between the range of dates .One date is constant the other one is from prompt.The formulas I used to calculate values are:

For YTD,

=[measure] Where ([year]=[p_year] And [month_no]<[p_month] And [day]<=31) + [measure] Where ([year]=[p_year] And [month_no]=[p_month] And [day]<=[p_day]).

For MTD ,

=[measure] Where ([year]=[p_year] And [month_no]=[p_month] And [day]<=[p_day])

For QTD,

=[measure] Where ([year]=[p_year] And [Quarter]=[p_quarter] And [day]<=[p_day])

where, [year] = calendar year ,[p_year]=prompt year, [month]= calendar month ,[p_month] = prompt month , [day]= cal day , [p_day] =prompt day ,[p_quarter] = Quarter in which the prompt date lies.

The problem I am facing is , unable to fetch the accurate values for the variables.

Could someone tell me whether the formulas I am using is correct?

Regards,

Vinaya

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 14, 2017 at 01:48 PM

    Hi Vinaya,

    Try to use the formulas from this blog to get the MTD, QTD and YTD. You'll have to replace the Current Date with Todate(Userresponse("Enter Date");"") though. After that, you can easily get the Measure values.
    https://blogs.sap.com/2014/01/21/time-variablesdimensions/

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 15, 2017 at 01:38 PM

    Please calculate such kind of calculations in back-end (universe or BEX query)

    Add comment
    10|10000 characters needed characters exceeded