topic How can I make a date selection that lies between the date values in my data? in QlikView

How can I make a date selection that lies between the date values in my data?

Mon, 15 Jun 2009 22:12:48 GMT

I've got a rather large data set with dollar amounts that span a date range like this…

Start End Amount

01/01/2009 01/07/2009 $70,000.00

…but I need a way to let the user key in (either in list box or input box) a date or dates between the ranges to see daily amounts that might look like this:

Date Daily Amount

01/04/2009 $10,000.00

or even...

Date Range Amount for the 3 days selected

01/04/2009 to 01/06/2009 $30,000.00

Since the data doesn't actually have discrete date values in between, how might I permit this type of date selection in QlikView w/o writing code to produce a giant new data set with a single row for every possible day & amount between each date in the range? Is there a way to simulate the date values, or map an entry from an input box to the corresponding dates in the data?

Thanks,

Jim

How can I make a date selection that lies between the date values in my data?

Oleg_Troyansky — Mon, 15 Jun 2009 22:31:02 GMT

Here is a possible solution (provided that you are using ver. 8.5 or higher):

1. Load the number of days in your date range, or even pre-calculate the daily average:

load

Start,

End,

Amount,

Start & End as PeriodKey,

Start - End + 1 as Days,

Amount(Start - End + 1) as DailyAmount

2. Load a Calendar Date field, with all possible dates within your total date range.

3. Join the two tables using "INTERVAL MATCH".

4. Enable the date from the calendar for user selection.

5. Construct a "Set Analysis" formula that will only enable those dates that are selected and are within the range.

Your calculation of the amount would be:

sum(<Daily Amount> * Count (number of days that are selected and are within the period between Start and End)

cheers,

Oleg

How can I make a date selection that lies between the date values in my data?

Tue, 23 Jun 2009 04:48:27 GMT

Thanks for the answer Oleg! This looks like it will work nicely. I had often seen IntervalMatch in the help docs, but the description of its syntax was never very clear to me. I had no idea it was capable of this until your example shed the necessary light.

Thanks again.

How can I make a date selection that lies between the date values in my data?

Wed, 09 Jun 2010 10:09:23 GMT

I've got a similar need than Jim G. and I'm trying to adapt your solution but I don't understand the 5th point:

"5. Construct a "Set Analysis" formula that will only enable those dates that are selected and are within the range."

I've got a list of persons with data historized like their adress for example and a calendar to enable user to choose a date. I want to display the correct adress of each person corresponding to the date selected.

The IF structure works in this case but I've got others data historized to display and then, calculation time increase a lot so I need to use intervalmatch.

So I don't understand how to construct a set analysis formula to enable only dates that are selected.

Thanks for advance,

Romain

How can I make a date selection that lies between the date values in my data?

Mon, 05 Jul 2010 09:44:08 GMT

HI Oleg,

Can you work it out for me here. I am facing same sort of issue here. I don't where to start also. Please help me!

http://community.qlik.com/forums/t/31125.aspx

How can I make a date selection that lies between the date values in my data?

Mon, 05 Jul 2010 09:56:29 GMT

Hi Jim,

Did you over come this issue? As you understand what exactly Oleg wants to do then I hope you can help me out here. Please look in to the below post and try to help me out. Thanks and in advance!

http://community.qlik.com/forums/t/31125.aspx