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