1 Reply Latest reply: Dec 16, 2009 6:27 PM by John Trigg RSS

    Basic questions around dates

      I'm new to qv and any kind of code/ expression development so these may be silly questions but I'm going to ask anyway. I'm trying to build some charts to display HR type data--specifically hires/terms by month/QTD/YTD by location. My hire date is in the format MM/DD/YYYY. How can I edit the script to pull only a certain range of data, such as hires from 2008 to current? Then, in my charts, how can I make them quarter to date, etc? I've tried messing with the expressions like InQuarterToDate ( date, basedate , shift [, first_month_of_year = 1] ) but I don't know what's what to even begin to update it. Can someone please explain what these fields are? Thank you!

        • Basic questions around dates
          John Trigg

          Kashby

          the most typical way to do this is actually to create additional date fields in the script (quarter, month, day, week etc). Then use the new fields in list boxes on your document to perform the limiting functions you need. In your install you should have a sample QVW called Online Sales (if not you may want to reinstall and perform a COMPLETE install to get all samples and tutorial files). The script in the Online Sales QVW has a tab for Calendar which has sample date manipulation code to built these additional fields from an Order Date - here are the functions (where OrderDate is the original field from the database record Order Header)

           

          Year(OrderDate) as Year,

          Month(OrderDate) as Month,

          Date(Monthstart(OrderDate), 'MMM-YYYY') as YearMonth,

          'Q' & Ceil(Month(OrderDate)/3) as Quarter,

          Dual(Year(OrderDate) & '-Q' & Ceil(Month(OrderDate)/3), Year(OrderDate) & Ceil(Month(OrderDate)/3)) as YearQtr,

          Week(OrderDate) as Week