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

[#2]

Hello Dan,

there is more then one step you can take. Lets start with a first solution. This would be to convert your incoming date columns into QV date fields. For this use at script level:

Load . . . date(FromDate, 'YYYYMMDD') AS FromDate; . . .

. In the same load script you should establish the data-periods you need ie with

Load . . . Month(date(FromDate, 'YYYYMMDD')) AS FromDateMonth; . . .

. The same you can do with your ToDate. Then you and your Users can select a FromDateMonth (or of course a week) directly in a list box and will indirect select all according dates (FromDate) to it.

The better way, in a next step, is to create your own calender (a calender for each date-field you need). There are many threads and post concerning this topic.

HtH for the first approach

Roland

[Anonymous]

Thanks Roland for quick response. 🙂


I forgot to mention that I allready have the date in both integer and true date format in load. Sorry.

Still I am not sure how this helps me. Please forgive me if I have misread your reply.

Look at one line of my base table

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

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

(Fromdate/todate is true dateformat)

If my user now ask for any customer with recuring order for year 2010 week 49 the above line should apear.
If I use the date field in the spesific line It would be year 2010, week 1.
It should recognize the above line and place it in week 49, since the selected period is between the from and to date.

Best regards
Dan

[#4]

Hello Dan,

now I know a litte bit more. But this I could have known earlier if I had read your first post more carefully, sorry!

Your users want to specify a week and then all rows should appear in your (straight) table with the selected week between fromdate and todate. For this purpose I would use a variable event trigger. Let me introduce this proceeding with a date value instead of a week. With a week value it works of course as well. First you define a variable. Next you define a Variable Event Trigger (settings --> document prop --> trigger --> Button "OnInput") with two selection actions. First action selects the according selection for the FromDate (with a searchstring like

='<=' & vDate & ' >=01.01.2010' ).

Second action defines the selection for the ToDate. When using an input box for your variable the trigger should fire (= execute the actions) and your two dates (and the according rows) should be selected.

HtH

Roland

[Anonymous]

Hi Roland

Trying to understand what you want me to do. I will post some screendumps so you can see what I am working on.

I cant get the inputbox to do anything at all, so obviously I am doing somethng terrinble worng. But what?

Main desktop:

Variable settings:

onInput: ='<=' & vStartDate & ' >=01.01.2010' )

Inputbox:

Thank you for your patience.

Dan

[#6]

Hi Dan,

this looks great. But I am afraid you are using an older version. I build a little example with V9SR6. Would you look for your version (--> help --> about QV). Perhaps it is possible to update.

RR

[Anonymous]

Version 8.50.6206.5

Dan

[Anonymous]

I feel I am so close...

I got the variable vStartDate to interact with the data.

Below you will find my desktop. Now I have included the straight table that shows all current test data.

My main obejct is the pivot chart.

When I enter a date in the StatDate input field the pivot chart alters.

But it only do a = test. I still fight to get it to check for intervals.

My measurement expression

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

My idea is that the user enters both start and end date and then the appropriate fields show.

I.E start 04.10.2010, end 11.10.2010, should show the summary for the 5 first rows. Two customers.

Dan

[Anonymous]

Knowing that it was a long shot in the dark, I also tried to alter my expresion to

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

Needless to say it did not help at all.

Dan

[#10]

Hello Dan,

I put an SET Analysis Term to support you:

sum({ <FromDate= { "$(= '>=01.01.' & Year(vDate))" }, ToDate= { "$(= '>=' & vDate & ' <23.03.2011')" } >} Value)

As far as I remember SET Analysis was introduced in 8.5. (the Triggers above did come later). You should be able to use the term above in your expression. Note that the value of the second date "23.03.2011" is for my testing only. Change it to a proper val i.e. "999999".

One hint: In V9 (I am currently using a stable version QV9SR6) and in the brandnew V10 there are a lot of new features. Think about an update!

RR