5 Replies Latest reply: Jun 4, 2009 12:16 PM by Silje Mari Sunde

# How do I calculate Year to Date?

Currently Being Moderated

Dear Colleagues,

I need to calculate Year to Date-revenue. Date is a prompt input, so the customer can choose which ever day he wants. How can I get revenue YTD based on the input? Is this a BODI assigment more than a BO assignment?

Looking forward to give you some points!

Regards Silje

• ###### Re: How do I calculate Year to Date?
Currently Being Moderated

Silje,

You don't mention the database vendor, but for example in SQL Server, there is an item called "datepart" and can extract the "Year" (yy) value from a valid date.  As such, suppose you have a column in your database called "ActivityDate" as a date-type.  Then you can construct a filter such as:

`where datepart(yy,ActivityDate)=datepart(yy,getdate())`

Thanks,

John

• ###### Re: How do I calculate Year to Date?
Currently Being Moderated

Hi Silje,

Normally you would build objects like this in designer (oracle syntax):

```sum(case when tab.datecolumn
between (@prompt('give ytd date','D',,mono,free)-to_number(to_char(@prompt('give ytd date','D',,mono,free),'ddd'))
and @prompt('give ytd date','D',,mono,free) then tab.amountcolumn
end)```

So basically sum all the amounts only if they are between the start of the year and the given date.

You use simple date manipulation to get to the right when condition, it can be based of a prompted date,

but also of the sysdate (current date).

There are dozens of variants here, you can build

year to date amounts (this year, last year, given year),

period amounts (given period, current period, last full period),

week amounts (current week, last week, last x weeks), full year amounts, etc.

you get the idea...

Hope this helps,

Marianne

P.S. should you wish to do it in the report, use a prompted date object in the query & select the date to compare in the query (note that you might get an awful lot of row back here)

sum(<amount object> where (<date object> between <given date>-ToNumber(FormatDate(<given date>,"ddd")) and <given date>)

• ###### Re: How do I calculate Year to Date?
Currently Being Moderated

Hi John and Marianne,

Thank you for answers, I will look into both suggestions today. The datawarehouse is based on DB2 on Z/os, if that matters.

I am thinking of trying to solve it in the universe, for performance issues. I've also thought about an aggregated table in BODI giving me the numbers I need.

Regards Silje

• ###### Re: How do I calculate Year to Date?
Currently Being Moderated

Hi Marianne,

What do you mean by:

<date object> and "ddd" in your formula?

sum(<amount object> where (<date object> between <given date>-ToNumber(FormatDate(<given date>,"ddd")) and <given date>)

I guess <given date> is the prompted date?

Regards Silje

• ###### Re: How do I calculate Year to Date?
Currently Being Moderated

This is how I solved it. (DB2 database)

sum(CASE WHEN

(@Select(Settlement Year\Year)=YEAR(@prompt( 'Enter End Date:','D',,Mono,Free,Not_Persistent,{},User:0))

AND

@Select(Settlement Day\Settlement Date Dt) <=  @Prompt( 'Enter End Date:','D',,Mono,Free,Not_Persistent,{},User:0)

)

THEN

DWF_ORDER_LINE_FACT.NET_AMT

END )

Silje