topic Re: Date range by single date field in database table in QlikView

Date range by single date field in database table

Sun, 14 Jun 2015 13:13:34 GMT

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.

Re: Date range by single date field in database table

MarcoWedel — Sun, 14 Jun 2015 13:22:29 GMT

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

Re: Date range by single date field in database table

Sun, 14 Jun 2015 13:32:12 GMT

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.

Re: Date range by single date field in database table

antoniotiman — Sun, 14 Jun 2015 13:52:23 GMT

Hi Amit,

see attachment.

Regards,

Antonio

Re: Date range by single date field in database table

MarcoWedel — Sun, 14 Jun 2015 14:41:06 GMT

one possible solution using alternate states could be:

hope this helps

regards

Marco

Re: Date range by single date field in database table

Sun, 14 Jun 2015 17:25:22 GMT

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

Re: Date range by single date field in database table

Anonymous — Sun, 14 Jun 2015 19:47:28 GMT

Hi Amit

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

Regards, Alex

Re: Date range by single date field in database table

MarcoWedel — Sun, 14 Jun 2015 21:47:10 GMT

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

Re: Date range by single date field in database table

Mon, 15 Jun 2015 16:25:20 GMT

Good to identify.

Re: Date range by single date field in database table

MarcoWedel — Mon, 15 Jun 2015 18:23:28 GMT

Please close your thread if your question is answered.

Thanks

Regards

Marco

Re: Date range by single date field in database table

Tue, 16 Jun 2015 08:05:29 GMT

I have QV personal edition. Can you give any video, so that I can understand this answer.