Variables and Dates

[racer25]

Hi

I am a little new to this and generally successes I have had are as a result of readingf through the forums and picking out the information I needed. This time however I think its a bit more specific....

I am working on an Income application that stores earnings by INCOME_RECOGNITION_DATE I would like to have Input Boxes with LowDate and High Date which will allow user to enter say 01/01/2009 and 31/01/2009 and selection would be narrowed to this date range.

I have played around with a few options and can get this to work with a slider, however earnings span 10 years and it looks awful so hence the reason for the Input Boxes - simple enters 2 dates....

Thanks in advance to anyone who can help.

[Oleg_Troyansky · Accepted solution]

Racer,

unfortunately, at the moment the only available solution is using VBScript (or JavaScript) macros, and those are pretty nasty... If you can wait a few months, it will become much better in QlikView 9 with the new "Actions".

Meantime - could you train your users to use "Search" on the date field and type in a condition manually - for example:

">=01/01/2005 <=05/05/2008"

I know it might be "too much" for some users, but the only alternative is writing macros. If you really have to do it, open the APIGuide and look for the following APIs:

GetVariable - to retreive the values of the entered From and To dates

and

Select - to apply your selection on the Date field.

good luck!

Oleg

[#3]

Hi,

I have a similar problem, where I have the same two variables (startdate and enddate), but I want to use them to limit a range in set analysis.

I want to compare a normal selection range of dates, against a user entered range of dates based on the start/end dates.

Does this also need to wait for Qv9, or is it possible now ?

Cheers

[#4]

You can do all of this using Set Analysis. Every expression will need to include a Set Analysis restriction such as

sum({$<Date={">=$(Start)<=$(End)"}>} Income)

Jay

[#5]

The below VB macro should give you what you need. After you incorporate the below code into your app the only other thing you'll need to do is link the macro to the VB code. To do that you'd simply go to Settings/Document Properties/ then it depends on if you're in 8.5 or 9.0. I'm in 9.0 so I click on the "Triggers" tab and go down to the "Variable Event triggers" then click "Add Action(s)" under OnInput. Once your inside there click "Add" then select "External" then select "Run Macro". The name of your macro is "Date_Received_DateRange" without the quotes. You have to do that for both your "LowDate" and "HighDate" variables. The other snipit of code below is to clear the values in your input boxes when you clear all of the selections on your app. To link that part of yoru macro to the clear selections option simply go to your clear selections button (if you have one in your app) right click and go to the "Actions" tab, and follow the same instructions you did for your two date values above. The name of the macro will be called "input_clr" without the quotes.

'The below module allows the Moen team to enter in a start and end range for budgeting
purposes.
sub Date_Received_DateRange

 set ds_sd = ActiveDocument.Variables("LowDate")
 set ds_ed = ActiveDocument.Variables("HighDate")
 set ds = ActiveDocument.Fields("INCOME_RECOGNITION_DATE")

 ds.Select ">=" + ds_sd.GetContent.string + "<=" + ds_ed.GetContent.string
end sub
'Add this on to the clear button so the input values clear.
sub input_clr
'Clear Welcome Sent Date Input box
 set DSDib = ActiveDocument.GetSheetObject("IB02")
 DSDib.SetVariableContent 0,"",true
 DSDib.SetVariableContent 1,"",true

end sub

Hope this helps.

rob