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]

In pure desperation I even tried an if elseif statement, no luck of course.

It yelds results, but it does not take into account my selections,

Sum(if(vStartDate>=[From Line Date],[Ordered quantity - basic U/M],if(vEndDate<=[To Line Date],[Ordered quantity - basic U/M],0)))

Dan

[Anonymous]

Hi again

I am out travelng to day, and while midair I relaised that my if statement actually works as it is.

I have been testing with startdate 04.10.2010, end date 10.10.2010. In my if statement I say that I want startdate to be >= fromdate and enddate <= todate. In my case I would then hit.

Apparently I will need to use a week input to make this work.

If you look at one of my former posts you will se my an object with my entire dataset you wil find that if I type 201040 I should only get fem records.

(If i set a week to be 201040, this equals 04.10.2010 to 10.10.2010.)

So in short terms I need to enter a week, this have to translate to a start and end date.

Then I need to check if my fromdate is between this range.

But how to accomplish this i beyond my at present time

Dan

[Anonymous]

Roland

We have now updated to version 9

Dan

[#5]

Hello Dan,

NOW is the time. After some missunderstandings I can present your adapted application.

I did some things: first I created a calender for selection purpose. See details in the script tab two. Note that I did a partial load (remove the "add" before loading). The whole thing is quick (and dirty) I am sure you can refine it. With this calender you do not need any variable, only a listbox. For testing purpose my "listbox" is a tablebox to show WeekLastDay and WeekFirstDay as well.

Second take a look into the expressions. It will take you only some seconds to understand. Note that your datefields are in reality timestamps. I would prefer to convert them into dates while loading. It's a very easier handling unless you need the timevalues. Otherwise load all dates as dates.

Lessons learned:

I should have ask earlier for an exam application. Communication is hard enough between people in their own language but even harder in a foreign language.

RR

[#6]

Hello Dan,

I forgot: Congratulation for upgrading. You will detect a lot of new feature. My two favs are the SET Analysis and triggers. And the server issues are much more professional.

Enjoy, Roland

[Anonymous]

Hi Roland

Thanks for you help, I wil look into this soulition.

Regarding timestamp, I do not need that, need only dates.

Language is much more a barrier then real borders are.

Next time I will post an exam first thing.

Dan

[Anonymous]

Thanks Roland for your input.

I will try to alter it so I can ask for year/week in your listbox.

If I can figure that one out, then I can send it for testing.

I tried to remove the add, actually I tried to remove both both I see no difference in the listbox.

Dan

[#9]

Hello Dan,

'add' in load script is used for partitial loading. This is the only way for me to add data to your application without loosing your data.

To show year and week in the listbox you can code like this:

load . . .year(date(today() + rowno()-150)) & '-' & week(date(today() + rowno()-150)) as SelWeek];

RR

[Anonymous]

hey, that works well.

Now I have a few more questions if you feel to reply.

1: When I remove add from code I get an error, normal?

2: Even with both add statemenst I get an error saying "Table not found. Drop table statement" Why?

3: How can I get my year-week listbox to also include years to come? Now it contains this years setup, but I would lke it also to show 2011? I will then have year in one list box and week in another.

Allmost forgott your expression. I think I understand it but to be on the safe side:

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

means

evalute [From Line Date] if it is greather then or equal to SelWweekFirstDay and less then SelWeekLastDay then SUM(order qty), right?

Much appriciate all your input. I have learned more about QW these few days then my entire last month I think.

Dan

[#11]

Hi Dan,

1. - 2. :I used the "add"-keyword to be able to ADD data to your data. To create the calendertables and to add to your data. If you now are reloading in a normal manner you do not need any add-keyword. I forgot to mention: then you do not need the two "drop table....." anymore. See Online-Help for details.

3. For testing purpose I used the autogenerate() - function which generates automatically new rows according to the load - statement. If you want more rows (dates) in the table "SelDateTbl" then simple increase the parameter. At the moment it is 150, set it to 300 to create 300 rows.

4. right. Don't forget to remove the & ' 00:00:00' when you replaced your timestamps with dates

You are wellcome.

RR