Set analysis date range problem

[thomastc]

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!

[#2 · Accepted solution]

Hi Thomas,

please see the attached example.

Good luck!

Rainer

[thomastc]

Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4

Thank you!

I cannot believe I got the $ and the 1 mixed up, I thought the $ was the full set of records.

Based on your document this is what I implemented:

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

Thanks again. I haven't tested the result set in detail but the numbers are 'looking' right

Thanks for helping out so quickly, you've made my day!