Post reply

LastYearYTD Sales

  • June 30, 2011 at 2:51 pm

    #243567

    I am using the following to try and pull LastYearYTD amounts shipped but it is returning Total units shipped from last year.

    =Sum(CInt(IIF((Year(Fields!billed_dt.Value) = Year(today) -1),fields!qty_to_ship.Value,0)))

    What am I doing wrong?

    Thank you for your help

    Mike

  • June 30, 2011 at 2:54 pm

    #1346763

    part of what you posted says Year(today) -1

    that would be 2010, correct? isn't that what you want?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2011 at 2:59 pm

    #1346765

    I am trying to get the Last Year YTD (1/1/2010 - 6/30/2010(using today as an example)) units shipped

    But according to the folks that read the reports, it is returning all units shipped for 2010.

    Thanks

    Mike

  • June 30, 2011 at 3:20 pm

    #1346782

    That doesn't look like SQL - because of the "IIF" included.

    But I think you're only specifying the year and not a range. So in effect you're telling the program to sum for the whole year and you need a BETWEEN (or similar operater).

    The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • July 1, 2011 at 5:36 am

    #1346955

    I am using this as an expression in SSRS 2005

  • July 1, 2011 at 9:03 am

    #1347078

    If you can move the date range to the parameters for either the whole report or this specific dataset you could do something like this in the sql query

    select sum(qty_to_ship)

    from tbl1

    where billed_dt between @FromDate and @ToDate

    and then set @FromDate to =dateserial(today.year-1,1,1) to give you the first day of the previous calendar year

    Then set @ToDate to dateserial(today.year-1,today.month,today.day) & " 23:59:59" to give you the date exactly a year ago. The time bit is needed if you store times and dates in the same field

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply