Set analysis date range problem

thomastc — Tue, 21 Dec 2010 13:13:34 GMT

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!

Set analysis date range problem

Tue, 21 Dec 2010 13:22:25 GMT

Hi Thomas,

please see the attached example.

Good luck!

Rainer

Set analysis date range problem

thomastc — Tue, 21 Dec 2010 13:37:02 GMT

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!

topic Set analysis date range problem in QlikView

Set analysis date range problem

Set analysis date range problem

Set analysis date range problem