Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Thomas,
please see the attached example.
Good luck!
Rainer
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!