topic Check for date intervals in QlikView

Check for date intervals

Anonymous — Tue, 30 Nov 2010 13:42:25 GMT

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

Check for date intervals

Tue, 30 Nov 2010 14:13:25 GMT

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

Check for date intervals

Anonymous — Tue, 30 Nov 2010 14:32:01 GMT

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

Check for date intervals

Tue, 30 Nov 2010 16:28:04 GMT

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

Check for date intervals

Anonymous — Wed, 01 Dec 2010 10:12:28 GMT

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

Check for date intervals

Wed, 01 Dec 2010 11:42:33 GMT

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.

Check for date intervals

Anonymous — Wed, 01 Dec 2010 11:50:41 GMT

Version 8.50.6206.5

Dan

Check for date intervals

Anonymous — Wed, 01 Dec 2010 12:01:56 GMT

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

Check for date intervals

Anonymous — Wed, 01 Dec 2010 12:27:19 GMT

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

Check for date intervals

Wed, 01 Dec 2010 12:52:15 GMT

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!

Check for date intervals

Anonymous — Wed, 01 Dec 2010 13:01:45 GMT

Thanks Roland will try this one.

Regarding update, I just got the word that we are set to upgrade tomorrow. Version 9.

Dan

Check for date intervals

vidyut — Wed, 01 Dec 2010 13:04:36 GMT

Hi,

Instead of going Set Analysis way, You could create another table (linked to the Orders table) which has Week, Product, Order, Quantity details for all the coming years. This could be done by using Interval Match on dates in New table, using the Order table as the FromDate-ToDate Map.

Vidyut

Check for date intervals

Anonymous — Wed, 01 Dec 2010 13:29:48 GMT

Tried it, but I only get 0.00 values in return.

Set analysis is completely new to me so I will have to read up abit on that part.

But here is my expresion.

sum({ <[Line Date, From]= { "$(= '>=01.01.' & Year(vStartDate))" }, [Line Date, To]= { "$(= '>=' & vEndDate & ' <12.31.2011')" } >} [Ordered quantity - basic U/M])

Maybe you would be so kind and walk me thru the code so I understand what happends?

Dan

Check for date intervals

Wed, 01 Dec 2010 14:14:28 GMT

Hello Dan,

you are allright. I was searching for a while but now with success. I am using ONE datevalue vDate in my expression and you are using two dates. Replace your vEndDate with your vStartDate and it will work (hopefully ;-)).

SET in two words: in an expression you can use SET analysis, a mighty tool but sometimes a little bit cryptic. Look at

sum( {< MyYear= {'2010'}> } Value)

This means the well known sum. In the outer {} there is the SET-Formula. The <> include your exceptions from users choice. Here regardsless which MyYear is selected, in this expression is only '2010' "counting". SET fomulares can be combined in many ways and nested from dusk till dawn. Good Luck !!

Hint: To see whats happening in your expression leave (while testing) the label of your expression empty. This helps sometimes.

Check for date intervals

Anonymous — Wed, 01 Dec 2010 15:09:21 GMT

Your tip about leaving the expression name blank was useful.. Now I see more what your example return in code statement

Your code:

sum({ <[From Line Date]= { "$(= '>=01.01.' & Year(vStartDate))" }, [To Line Date]= { "$(= '>=' & vStartDate & ' <31.12.2011')" } >} [Ordered quantity - basic U/M])

Result code:

Event when I changed my StartDate input it returned the same code, and value 0.00

I therefor changed the expression to :

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

with following result code:

Check for date intervals

Anonymous — Wed, 01 Dec 2010 15:14:22 GMT

argg, hit the post button to early..

The result from my post above:

Still I get the value 0.00 but now at least I can see that what ever a user supply as input results in changing the expresssion.

(by the way the norwegian date format is day.month.year)

As you see from the below sniblet I am getting closer, but still that final touch is missing.

Check for date intervals

Wed, 01 Dec 2010 17:44:29 GMT

Hi Dan,

now I am a little bit confused. You told me in a former post ....

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.

....

Because you wrote the date "20101101" ... "would be year 2010, week 1" I read it unfortunately as "01.01.2010". Sorry, this wasn't carefulls but I derived that you want your daterange to start from the beginning of the year of your startdate (now you know the reason for the '01.01." & year(vDate) in my expr.). This was obviously wrong.

As I know you now want to see all the rows where the selected week is between the from and todate.

To your latest post:

The syntax is correct (Yes!!) because your straight table shows valid values in the column (ok, they are all 0, not what you expect) One question: Shouldn't you consider the rows with '9999999' as a legal Enddate. This would be similar to that: ... [To Line Date]= { "$(= '<=' & vEndDate), '9999999' "} ....

To shorten the whole thing, can you post a little exam application with one or two exam selected From(To)dates and the results which you are expecting?

BTW: I like the norwegian date (and of course the norwegian people).

Check for date intervals

Anonymous — Wed, 01 Dec 2010 20:43:54 GMT

Hi Roland

Again I have to thank you for your patience with a novice like me. Aappriciate it.

I am sorry for the confusing posts I may have written.

To date no longer holds 99999 entries, I have set a code that changes them to 2 years after from date

I have a created an application file, but not sure how i can send it to you.

Dan

Check for date intervals

Anonymous — Wed, 01 Dec 2010 22:15:42 GMT

I have gone through the complete thread. Somewhere Dan mentioned Users want to search the data on the basis of week.Why dont you extract one week column with the week function in your load script and map it to list box as a filter criterion.Now sure whether I am on same page.

As I tried once week filter and rest values i.e. summation of say orders and all will be calculated by Qlikview on the fly if you set the expressions correctly.

--Swapnil

Check for date intervals

Anonymous — Thu, 02 Dec 2010 06:27:21 GMT

Morning Swapsjosh

This sounds good, but I am afraid I do not fully understand how this works.

I have two datefileds, from and to.

A user can supply any given week and ith that week lies betwwen these dates then the line is taken into account.

Dan