How do I calculate Year to Date?

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[MariannevL]

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...

So get your wish list ready and talk to your designer...

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>)

[Former Member]

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