Year To Date & Month To Date

building semantic layer
1

i have done a variable for current date " Now() " and then i calculated the current week

Now i need to know How to calculate Year to date And Month to date in SAP IDT .

Like in attach pic
YTD.png

zahmed (BOB member since 2018-01-14)

2

What type of data source are you reporting against - Oracle, SQL Server, HANA, BW, etc.?

3

i made Remote data source between a HANA and SQL to retrieve data from SQL to HANA so yes my database is HANA now .

zahmed (BOB member since 2018-01-14)

4

In that case, take a look at this blog post:
https://blogs.sap.com/2014/03/10/how-tocalculate-ytd-mtd-anytd-using-date-dimensions/

5

Thanks so much :+1: , but am building a universe based on HANA and am not dependable on User input . i need a report on webi to have updated WTD and MTD and YTD updated without user input . depending on today date

zahmed (BOB member since 2018-01-14)

6

I’ve not written any HANA SQL so don’t know what functions you have available. Here’s the pseudo code that I’d use for sales year to date and month to date objects.

YTD:

SUM(CASE WHEN Year(TransactionDate) = Year(NOW()) and TransactionDate < Now() THEN TransactionValue ELSE 0 END)

MTD:
SUM(CASE WHEN YearMonth(TransactionDate) = YearMonth(NOW()) and TransactionDate < Now() THEN TransactionValue ELSE 0 END)

YearMonth could be obtained by formatting your date as a string in YYYYMM format, so that this month would format as 201901. I’ll let you figure out how you do that in HANA code. :slight_smile:

7

:smiley: Thanks Mark

zahmed (BOB member since 2018-01-14)