Check for date intervals - Page 2 - Qlik Community

Anonymous · ‎2010-11-30

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 · ‎2010-12-01

Thanks Roland will try this one.

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

Dan

vidyut · ‎2010-12-01

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

Anonymous · ‎2010-12-01

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

Report Inappropriate Content · ‎2010-12-01

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.

RR

Anonymous · ‎2010-12-01

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:

Anonymous · ‎2010-12-01

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.

Report Inappropriate Content · ‎2010-12-01

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).

RR

Anonymous · ‎2010-12-01

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

Anonymous · ‎2010-12-01

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

Anonymous · ‎2010-12-02

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