18 Replies Latest reply: Jun 17, 2014 9:30 AM by Keith Watson RSS

    Best way to count "Order Bank" over time

      We have a concept in our business called "Order Bank". This is simply orders that have been confirmed, but not yet delivered.
      The concept has been extended a little in QlikView by having a date selector that allows you to look at the Order Bank at a point in time by looking at Orders that have been ordered before the selected date, but have a delivery date after the selected date or a delivery date of NULL using the following Set Analysis expression:

       

      ({$<OrderStatus={"Confirmed Order"}, OrderNotesConfirmationDate={"<=$(vSelectedDateOrderBank)"}, DeliveryRecordLiveDate={">$(vSelectedDateOrderBank)"}> +
      <OrderStatus={
      "Confirmed Order"}, OrderNotesConfirmationDate={"<=$(vSelectedDateOrderBank)"}, DeliveryRecordLiveDateIsNullYN={"Y"}> } OrderNotesConfirmationDate) 

       

      What I need to do next is produce a graph (or table) that shows the "Order Bank" for each month. 

      Please can anyone help me understand the best way of acheiving this in QlikView?

       

      The main point I think I'm struggling with is that "Order Bank" doesn't have a date dimension in the actual data. So, although the calculation involves the "Confirmation Date" and the "Delivery Date", these are not the date dimension for the measure.

       

      For example, if we had 10 products ordered in Jan, 10 products ordered in Mar (none delivered in this period), the "Order Bank" for February would still be 10 products even though there are no "Confirmation Dates" or "Delivery Dates" that relate to February.

       

      For example, the graph (table) should look like this:

       

      Jan 2014          10

      Feb 2014          10

      March 2014      20

       

      I hope the explanation of my problem makes sense? Thanks in advance,

       

      Keith