Date range by single date field in database table

[#1]

I have a database table which has 2 fields orderDate and orderAmount. Now I need to create date range as from-order-date and to-order-date in 2 separate list box by using orderDate field. And also need sum(orderAmount) when customer select from-order-date and to-order-date from those list boxes.

orderDate	orderAmount
1/1/2015	1000
14/2/2015	4500
31/3/2015	200
30/4/2015	800
14/6/2015	3500

Anyone please provide video or process step by step.

[MarcoWedel]

Hi,

you could use variables and calendar objects to select start and end dates or use list boxes in alternate states to get the result you specified.

regards

Marco

[#3]

Thanks marco, but the main problem is when I choose a date from 1st list box then that date automatically selected in 2nd list box.

Consider that only list boxes and only those 5 date values, not calender object.

[antoniotiman]

Hi Amit,

see attachment.

Regards,

Antonio

[MarcoWedel]

one possible solution using alternate states could be:

hope this helps

regards

Marco

[#6]

Hello Antonio,

If you select :

From : 31/03/2015

To : 14/02/2015

It should bring nothing, instead it selects all the values.

I added a conditional in the expression : min(orderdate) <= max(orderdateto)

Regards

Kosmas

[Anonymous]

Hi Amit

I've prepared a file with the solution. Maybe it would suit you.

Regards, Alex

[MarcoWedel · Accepted solution]

interesting, so does my solution.

Thanks for pointing this out.

Changing the search string in the set expression into

=Sum({$<orderDate={"=orderDate>='$(=only({asStartDate} orderDate))' and orderDate<='$(=only({asEndDate} orderDate))'"}>} orderAmount)

seems to work though.

hope this helps

regards

Marco

[#9]

Good to identify.

[MarcoWedel]

Please close your thread if your question is answered.

Thanks

Regards

Marco