topic How to select relative dates? in QlikView

How to select relative dates?

Wed, 23 Mar 2011 22:05:34 GMT

I want to be able to select relative dates. For example: "Past 7 Days", "Past 30 Days", etc. Would like to have a drop down list such that when the user selected "Past 7 days" a selection is done on the date field as displayed in the screen shot below. How do I select the previous 7 days when the user selects "Last 7 Days" from the drop down. Or is there a better way to do this. Thanks.

How to select relative dates?

Anonymous — Thu, 24 Mar 2011 02:19:32 GMT

1. Create a variable vRelativeDays (you have it). Allow to enter only positive integer.
2. Create input box with this variable. Use label like "Last N Days".
3. In the document properties, tab Triggers, attach the next trigger to the vRelativeDays variable, on input:
Select in field: Date
Search string: ='<=' & date(today()) & '>=' & date(today() - vRelativeDays)
As soon as you enter a number in the input box, the Date range will be selected.

Certainly you can restrict variable values to a list, for example allow only 7, 30, ... I prefer to have maximum flexibility.

How to select relative dates?

Thu, 24 Mar 2011 03:10:31 GMT

Hi Jims,

A quick solution (but looking for better one as well) would be to build a dedicated table to pre-defined your selection.

And replace your inputbox by a simple listbox (containing the field 'Selection') or a MultiBox.

Script would be:

MaxDate:
Load Max(Date) As MaxDate
Resident Facts;

Let v_Max=peek('MaxDate');
Drop Table MaxDate;

As of:
LOAD
Date($(v_Max) - iterno() + 1) as Date,
'Last 7 days' as Selection
AutoGenerate(1)
while iterNo() < 7
;

And then with a concatenate statement, you continue to build your pre-defined selection.

I was trying to execute the following script (simpler), but I can't make it work (I guess it is because of the Max() function; which prevent the iterno() counter to iterate). If someone can confirm or correct me.

As of:
LOAD
Date(Max(Date)) - iterno() + 1) as Date,
'Last 7 days' as Selection
Resident Facts
while iterNo() < 7
;

How to select relative dates?

Thu, 24 Mar 2011 03:23:39 GMT

Hi Michael,

Excellent, I did not know you could put a trigger on a input box!

Could you help to understand why the following code does only produce 1 single observation.

As of:
LOAD
Date(Max(Date)) - iterno() + 1) as Date,
'Last 7 days' as Selection
Resident Facts
while iterNo() < 7
;

It is because I am using the Max() function, right?

How to select relative dates?

Thu, 24 Mar 2011 16:58:03 GMT

Michael, thanks for the suggestion. I have tried and it is not working. Below is my code, I have tried the Date() function with and without the format string. Any suggestions? When I change the integer value in the input box associated with the vRelativeDates variable no selection is made in the RollupDate field.

Thanks, Jim

How to select relative dates?

Anonymous — Thu, 24 Mar 2011 17:36:05 GMT

Jim,
The expression looks fine. Except, I don't like the '=' before the field name - try to remove it.
If it doesn't help, quesions:
- Is RollupDate the correct field name? I see Date in your first post. I understand it could be just a lable - just to make sure.
- Is format of the RollupDate exactly 'MM/DD/YY'? If it is not the right one, it can't work.
- Did you assign this action to the variable vRelativeDates?
You can test the action itself by assigning it to a button or text box.
(I have tested the solution before posting it - it works)