Check for date intervals

[Anonymous]

Hi all

I have only been workng with qlickview for little over a month, so bear with me if my question seem stupid or misplaced.

I have a table that lists all customers and orders that are recuring. That means that a customer can place an order for say item 100 and ask for delivery every monday until a stop order is issued.

The table would look like

customer, order, item, qty, day, FromDate, ToDate

1000,000123,100,4.0,1,20101101,9999999

1000,000322,234,5.0,1,20101101,20101231

My users want to select a week, say 201049. The report should then show all he customers, item, qty for each day that week.

How do I get this to work. I guess I need to create a variable that I can use to search between the two dates.

If it is easier to use two seperate calenders, from and to,in order to select the period, then that is just fine.

If the ToDate is set to 9999999 it will be translated to fromdate + 2 years.(allready taken care of in the datamarts.

Hope anyone can help me with this

Best regards

Dan

[Anonymous]

Roaland, I now got it to work more or less as I pictured it. Thanks for your help.

One last question, so far, How can I get the year listbox to expand til 2011,2012. I only get 2009, 2010 now. I would like it to open for looking into the future.

Dan

[#3]

Hi Dan,

glad to help. I am learning in such a process, too.

For increasing your Selection-Year increase the parameter of the autogenerate() - function in the load statement. As I mentioned in my last post i.e. to 300 iterations or to get dates from 2012 to 600.

year(date(today() + rowno()-150)) & '-' & week(date(today() + rowno()-150)) as SelWeek
autogenerate(600);

RR

[Anonymous]

Thanks, works like a charm.

I have had a talk with the group that will be using this report.

We have a from and to date for each line. But in real life they only use from date, and that is how I have created the report.

However, it is possible to se a to date, a stop date.

Attached to this reply you will find my latest version of the report. In this dataset you will find that a valid to date have been set for item 215.

If I choose any week after 01.01.2011 this item shoud not be included in the report.

I tried to create an expression with both from and to date, but no luck of course.

I`ll bet you know how to do this.

Dan

[#5]

Hi Dan,

the test-expression of your pivot wasn't too bad. I changed to things, one syntax error and one semantic error. So explore and try my expression that I tested in your "Table Content"-chart, of course without a label 😉

sum({ <[From Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" }, [To Line Date]= { "$(= '>' & SelWeekLastDay &' 00:00:00')" } >} [Ordered quantity - basic U/M])
//sum({ <[From Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" },<[To Line Date]= { "$(= '<=' & SelWeekLastDay &' 00:00:00')" } >} [Ordered quantity - basic U/M])

Regards, Roland

[Anonymous]

Thanks, I should have seen that syntax error I guess.

Now at least I can publish this first step, and start exploring the step I know will come.

My current data cotains records for recuring orders, as well known now. We also have data, in anoter table, with stop ordrers.

This first step takes into account that one order can last for an infinite time of period. However, during this infinite period you will have periods where the customer wants to stop his delivery. i.e. christmas week.

Say it is a school that wants a recuring order, but during christmas, school is closed, so for this limited time period they issue a stop request.

Techincally we now have a table with a customer, stop date from, stop date to.

I am sure that I will have to build a function that verifies that my selction in my report is not inluded in the stop table.

Dan