Skip to Content
0

# How to calculate YTD,MTD and QTD values

Jun 13, 2017 at 01:31 PM

267

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

* Please Login or Register to Answer, Follow or Comment.

### 2 Answers

Mahboob Mohammed Jun 14, 2017 at 01:48 PM
0

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

Share
Koen Hesters Jun 15, 2017 at 01:38 PM
0

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

Share