topic Re: Show Dates Less Than or Equal to Selection in QlikView

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:15:48 GMT

Hi All,

I have a date field [LastModifiedDate]. When i make a selection on this feild it drills the data down to the specific dates that are selected.

What i want to do is to be able to show all dates less than of equal to the dates i have selected.

e.g. when i select 16Mar i want it to show all record with a [LastModifiedDate]<= 04Jan.

The way i have it at the moment it only shows data equal to 04Jan

I have attached a sample of the data........

Thanks in advance

James

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:24:40 GMT

do like this..

goto settings-> document properties -> field event triggers and click the date field and add a action "select field" action.

give the field name and value as an expression what u want.

i think it may helps u, if it is not proper to u plz ignore my ans.

regards

A'run'

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:32:05 GMT

Not sure i understand

would you be able to do it on the attached file and send it back??

Many thanks

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:37:06 GMT

Hi James,

A good way around this is to create an Advanced Search attach it to a bookmark and then apply it with a button.

Step-by-Step:

1. Create a data island for your dates. I.e. if you have a calendar and the field is named Date, do a resident reload of the Date from the calendar where you alias the field to, e.g., SelectionDate.

2. Create an object in the front-end where you can make a selection on this SelectionDate, e.g. a calendar.

3. Put in a Listbox for Date (from the calendar), right-click on it and select Advanced Search (Ctrl+Shift+F). In the advanced search box, put in a search string such as "=Date <= SelectionDate" and click on Go. This should make the right-selection on the Date field. N.B. Keep the Advanced Search box open! Do not close it.

4. For visual purposes, right-click on the SelectionDate and clear the selections. (This should still hold your Date selections unchanged).

5. With the Advanced Search box open, create a bookmark. This will include the advanced search string in the bookmark.

6. Create a button which applies this bookmark.

With the combination of the calendar object based on SelectionDate and the button which applies bookmark, you should get your desired result.

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:52:37 GMT

Check attached QVW.

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 10:57:41 GMT

Struggling to make it work... i believe i have followed your instructions, up to point 3 where it should be making my desired selections.

using this expression in advanced search on Last modified date "=[Last Modified Date]<=[Selection Last Modified Date]"

See attached file

Thanks again

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 11:13:03 GMT

Hi,

This is what your script should look like. Try the step-by-step with the new script it should work.

Table:
LOAD
[Claim Number] AS [Claim Number],
[Last Modified Date] AS [Last Modified Date]
FROM
[QV Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

DateSelection:
LOAD
[Last Modified Date] AS [Selection Last Modified Date]
Resident Table;

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 11:28:31 GMT

New file. Step-by-step in tabs w/ screendumps. Script should be as previous. The DateSelection needs to be a data island (i.e. not connected to the rest of the data model).

Copy paste the script I gave you above and use this new file for a step-by-step with screen dumps.

hope it helps.

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 11:37:03 GMT

i'll give it a go

you have been really helpful!!

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 11:40:27 GMT

No probs. This date selection issue is part of the "darker", obscure arts of QlikView and is not readily documented. It's that SAP style of clients want to select start & end dates and see all values inbetween. This is the only workaround I know of. Tell me if you got it to work.

Hope you get joy.

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 11:59:22 GMT

Sorry to butt in but I think this might be an easier solution. Create a data island as mentioned earlier (but use distinct to only load one occurence of each duplicate value)

DateSelection:
LOAD DISTINCT
[Last Modified Date] AS [Selection Last Modified Date]
Resident Table;

Create a list box for [Selection Last Modified Date]

Settings>Document Properties>Triggers>add 'OnSelect' action to field event trigger for [Selection Last Modified Date]>Add action 'Select in field' with field [Last Modified Date] and search string

=if(not(isnull([Selection Last Modified Date])),'<=' & [Selection Last Modified Date])

Regards,

Gordon

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 12:03:38 GMT

Thanks for that technique Gordon. Am glad there is an alternative to Advanced Search and button.

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 12:41:34 GMT

Got it working.... your example helped a lot....

Show Dates Less Than or Equal to Selection

Thu, 17 Mar 2011 12:43:49 GMT

Another Great Solution.... Much appreciated guys!!!

Re: Show Dates Less Than or Equal to Selection

Mon, 16 Jan 2012 17:59:53 GMT

You can make a button with a select field action. Then pick the "dates field" you're using and use this as the search string

='=dates field<=today()'

Use the expression exactely as above, replacing dates field with your field name and when you click the button it will highlight all dates less than or equal to the current.

Re: Show Dates Less Than or Equal to Selection

Thu, 07 Feb 2013 17:52:15 GMT

Thanks, Brandon. This is exactly what I needed.