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

How do I calculate Year to Date?

Silje Mari Sunde
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?
    John Sanzone
    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?
    Marianne Loenen
    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...

     

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

  • Re: How do I calculate Year to Date?
    Silje Mari Sunde
    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?
    Silje Mari Sunde
    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?
    Silje Mari Sunde
    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

Actions