topic How do I anchor dates? in QlikView

How do I anchor dates?

Thu, 21 Feb 2013 21:31:06 GMT

I am new to QlikView and have a question on dates. I see where I can build a master calendar, but what if I want to lookup data on a random date? I do not think a master calendar is my solution, mostly due to the nature of my metric. Below is an example.

I have Order Date.

I have Ship Date.

Then I have what would be calendar dates where I want to randomly measure service level.

Basic service level is calculated as Ship Date - Order Date i.e. # of days from Order to Shipment.

But on a given day, I want to find Service level measure as defined below:

Here is where it gets a bit more complex. The numerator is # Shipped on a given date of only those Ordered on that given date.

The denominator is the Total number of shipments on given date (this includes shipments from orders of past days).

My overall calculation is: (#Shipped today from only those Ordered today)/(Total # shipped today) where today is the given date. But I cannot use today() function. It has to be able to find any date in the past, preferrably from my list boxes of Ship Date, Order Date, or any other dates I have.

I.e. (#Shipped on Jan1 from only Orders on Jan1)/(Total shipped on Jan 1). But not using Jan 1 in the formulas.

I have a dispatch# which is attached to each shipment. So for the numerator, I am calculating Count(Distinct If(shipdate-orderdate=0, dispatch#, null())).

But the denominator is giving me problems. I cannot just say count(distinct if(shipdate='value', dispatch#, null())).

My 'value' has to be an anchored formula so that whatever day I click on my list boxes for Shipdate, orderdate, etc. this is the 'value' always searched in the denominator.

Make sense? Any help is greatly appreciated.

Re: How do I anchor dates?

Thu, 21 Feb 2013 22:03:28 GMT

From what you are describing it sounds like a variable would be your best bet. You could have a calendar object that uses the variable vSearchDate, for example, and then refer to that variable in your denominator set analysis.

Re: How do I anchor dates?

Thu, 21 Feb 2013 22:04:30 GMT

If you have a sample file, I could probably show you what I mean better than I can explain it.

Re: How do I anchor dates?

Thu, 21 Feb 2013 23:45:32 GMT

Thanks Rebecca, that would be great. I think your suggestion of SearchDate might be a possible solution. Silly question - how do I upload a qvw file into discussion?

Re: How do I anchor dates?

Thu, 21 Feb 2013 23:45:40 GMT

Thanks Rebecca, that would be great. I think your suggestion of SearchDate might be a possible solution. Silly question - how do I upload a qvw file into discussion?

Re: How do I anchor dates?

Thu, 21 Feb 2013 23:46:44 GMT

Thanks Rebecca, that would be great. I think your SearchDate idea might be a possible solution. Silly question - how do I attach a qvw file into discussion?

Re: How do I anchor dates?

Fri, 22 Feb 2013 13:58:27 GMT

Not a silly question. After you click Reply, click 'Use advanced editor' (top right above Reply box). It may ask you if you are sure you want to leave the page, so say yes. Then it will show an option to attach files below the Reply box.

Re: How do I anchor dates?

Fri, 22 Feb 2013 14:09:03 GMT

Here is a sample file. In the expression for the chart, I need to use the date variable in place of '1/1/2013'. But you migh have other suggestions as well.

Thanks for all of your help.

Re: How do I anchor dates?

Fri, 22 Feb 2013 14:35:12 GMT

It would be something like this, although you may need to play around with it to suit your actual data.

Re: How do I anchor dates?

Fri, 22 Feb 2013 17:29:15 GMT

Thanks Rebecca. This is extremely helpful.

One question on the variable you created, did you input the min and max based upon the data set I provided or was QlikView able to create these in some manner? When I created my slider/calendar object and selected vSearchDate, the min/max was not provided.

Just wondering if this is a necessary manual step for each data set I have.

Re: How do I anchor dates?

Fri, 22 Feb 2013 18:29:47 GMT

No I created them just to give you a small snapshot. You can either use an expression to calculate it or do what I did and enter static mins and maxes. It's located on the General tab of the properties. I think it generally defaults to Min 0 and Max 10, so of course you will need to adjust it. An example of an equation would be =Min(TOTAL ShipDate) for the min.