Date range query

[hopkinsc]

I have a START DATE and END DATE range which the user selects by 2 calenders. Each calender is mapped to 2 variables (vStartDate and vEndDate)

The calenders have min and max values mapped to a TRADINGDATE field (=Min(TradingDate) =MAX(TradingDate))

I am trying to put in an error message if the user selects an END DATE earlier than the START DATE. I have enabled the error message, but i am having trouble getting a calculated condition to work

I have tried the following

sum({$<TransDate = {">= $(vStartDate)"} * {"<= $(vEndDate)"}>} SalesValue)

Can anyone help me out with this please?

[Miguel_Angel_Baeyens]

Hi,

The following should work

sum({$<TransDate = {">=$(vStartDate)<=$(vEndDate)"}>} SalesValue)

Regards

[hopkinsc]

Hi, that doesn't seem to work either. it will not let me select the same dates, and it accepts an end date less than a start date. i tried swapping the LESS than and GREATER than symbols (< >) around so it looks like this :

sum({$<TransDate = {"<=$(vStartDate)>=$(vEndDate)"}>} SalesValue)

This seems to work apart from selecting the same date. How do write the condition so it accepts a date equal to or greater than START DATE?

[Miguel_Angel_Baeyens]

Hi,

To prevent the user to select incoherent dates, I'd do the check in a different conditional, or even in the Calculation condition of the object, so the set analysis is always like that, but the chart will not show any values because they won't make any sense.

What it does make sense also is that you may set a maximum and minimum date dynamically in your calendar objects, so once the start date is selected, and so stored in the vStartDate variable, it becomes the minimum for the end date, so the user will not be able to select one less than the other.

Hope that helps.

[hopkinsc]

Hi, thats a good idea to use Dynamic date selction in my calander. I'm really sorry, but how would i do that?

Also is it possible to clear the start and end dates?

The user can select either specific dates (start and end dates) or they can click on week numbers.

i have mangaed to use a trigger so that when the user selects a week number, the end date calender is cleared. But it doesn't seem to work for the start date.

I have used a field event trigger, on select, set variable , and the expression
=if(len(GetFieldSelections(TradingDate)), vEndDate).

that works great for clearing the end date, but if i use the same (but vStartDate instead of vEndDate) it does'nt work.

[Miguel_Angel_Baeyens]

I'd do as follows:

a) add an action (right click on the background of the sheet, properties, triggers) so when a week is selected, set variable vStartDate to take "=WeekStart(Weekfield)" as value and vEndDate "=WeekEnd(WeekField)"

b) add an action so when start date is selected, vEndDate is set to "=$(vStartDate)" so the user will never select incoherent dates.

c) in the start date calendar object, min is set to "=Min(Date)" and max is set to "=Max(Date)"

d) in the end date calendar object, min is set to "=$(vStartDate)" and max is set to "=Max(Date)"

Moreover, you can add a visible/hidden variable so the end date object will only appear if a start date is selected. The steps above should be enough, though.