2 Replies Latest reply: Dec 21, 2010 8:37 AM by Tom Cotterill RSS

    Set analysis date range problem

    Tom Cotterill

       

      Hello,

      I know similar questions have been asked before but I have been unsuccessful in implementing any previously suggested solutions.

      This is the problem: The application has a date select and I need to show the results for the current selection as well as that of the current selection for the previous year. I implemented a financial year comparison (current YTD against previous YTD) using set analysis which worked fine.

      However it falls over if a user selects a month or a quarter. So I tried to rework the set analysis using a date range based on a date field in my data called FullDate, which is a date field in the following format: 'dd/MM/YYYY'. I cannot get the date range set analysis to work correcly.

      Here is a breakdown of what I am doing.


      FullDate is defined in the load file as:
      'Date(FullDate) As FullDate'


      In the application I then create 2 variables for the start and end of the selected date range for the previous year:


      varPreYearFullDateStart =Num(Date(Addyears(Min(FullDate), -1), "dd/MM/YYYY"))
      varPreYearFullDateEnd =Num(Date(Addyears(Max(FullDate), -1), "dd/MM/YYYY"))


      As you can see I have converted the data type using the num function.


      Moving on to the expression. The basic expression for the current selection is:


      NumericCount(ActivityDateKey)


      As a starter I tried the below:
      NumericCount({$<FullDate={'>=$(=Date(varPreYearFullDateStart))'}>} ActivityDateKey)

      This returns exactly the same number as NumericCount(ActivityDateKey), something is not right there! If I augment the set analysis with the end date:

      NumericCount({$<FullDate = {'>=$(=Date(varPreYearFullDateStart<=$(=Date(varPreYearFullDateEnd))'} > } ActivityDateKey)


      Then expression returns nothing at all.

      In summary I need a set analysis example that uses a date range with a start and an end that returns the set of data for the previous year based on the currently selected date range.


      I have plenty of data by the way, five years worth.
      I am running QV version 9.00.7.

      Please ask if I can provide any more information, any help greatly appreciated! Thanks!