Best way to count "Order Bank" over time

[#1]

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

[MK_QSL]

It is difficult to work on your requirements without having data in hand.

Can you provide some sample data?

[#3]

You may like to start from point from my example

Of course take a look on demo apps on qlik www site.

Let me know if it helps.

Regards

Darek

[#4]

Hi Manish,

Sorry for the delay in replying...please find a sample QVW attached.

The Month and Year control which month the "OrderBank" is calculated for.

The Set Analysis expression is show in the text box underneath the table.

Best regards,

Keith

[#5]

Hi Dariusz,

Thank you for trying to help. Unfortuanately, I think the example you have shown me is slightly different to my problem, because your data contains the correct date.

I am going to investigate creating a solution by using scripting to calculate the aggregate figures using some sort of cartesian  join with a manufactured date dimension, but I wanted to ask if anyone who knows QlikView could do this sort of thing more quickly using some form of Set Analysis or other technique.

Best regards,

Keith

[#6]

Keith,

Maybe I just didn't understand precise your business requirements ....

Now i found your attached document. I see data. I understand more, I think.

In my opinion there will be possible to create your graph The best way to avoid misunderstanding will be if you share in excell few rows of data and sketch, how you graph for those data should look like.

regards

Darek

[#7]

Hello, Keith!
I attached solution for your example, I think I was right with your problem.

It's quite expensive for performance, but you can upgrade it.

Nikolay.

[#8]

Hi Darek,

Thank you for the reply. I hope I didn't cause any offence? I'm fairly new to posting on the forum, but your idea of an example in Excel is a very good suggestion. Please see attached...

Best regards,

Keith

[#9]

Hi Nikolay,

Thank you very much. That looks very good. I will check in more detail after the weekend, but thank you again.

Best regards,

Keith

[#10]

Now i can see what you need

Should be easy