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

[#2]

Keith, Nikolay,

it seems, that Nikolay solution gives exactly the same result, as I expected after seeing Keith last xls file and i hope it resolves your problem

I may only suggest to rearange a little your data model to have table like this:

Order Number, Transaction type, Date, Year_Month, counter

1,      Order,           2013-01-01, 2013-01, 1

1,      Delivery,      2014-01-01, 2014-01, -1

2,     Order,     2012-03-05, 2012-03, 1

2,     Delivery,     2012-04-07, 2012-04, -1

Assuming, that you may have no transaction  for some month you will need to have 2-nd table with full Year-Month list.

Year_Month

2013-01

2013-02

........

2014-12

You may generate it in the load script, like this:

new_table:

load

OrderNumber,

ConfirmationDate as Doc_date,

'Order' as type,

1 as Bank_counter,

Month(ConfirmationDate) As MonthDate,

Year(ConfirmationDate) As YearDate

resident transactions;

Concatenate

LOAD

OrderNumber,

DeliveryDate as Doc_date,

'Delivery' as type,

-1 as Bank_counter,

Month(DeliveryDate) As MonthDate,

Year(DeliveryDate) As YearDate

resident transactions;

Using this model your calculation will be simplier and i think faster, especially if you have more data.

In that model you will simple have expression like: sum(Bank_counter) and if you turn on Accumulation, QlikView should do rest of work for you.

regards

Darek

[MK_QSL]

Please check enclosed file...

Update : I have changed some dates as per your excel file....

[#4]

Dear Dariusz, Manish and Nikolay,

Thank you very much for your kind assistance.

I now have a beautiful graph in QlikView exactly as I wanted (please see the attachment if you're interested).

Thanks again,

Best regards,

Keith

[#5]

[#6]

Dear All,

I have a new requirement for my Order Bank - please could I ask for some further help?

Rather than display orders in the graph across the whole date dimension, I would like to display orders from the start of the selected year to the end of the selected month/year.

Please find example qvw attached.

In this example, if the user selects May and 2014 in the two multi boxes at the top of the screen, I would like to restrict the rows displayed in the right hand table ("Order Bank") to just Jan, Feb, Mar, Apr and May 2014.

I am trying to do this with a variable called vSelectedStartDate, Set Analysis and the expression in the Chart Table ("Order Bank"), but the set Analysis is not working for me.

Thanks in advance,

Keith

[#7]

Please execuse me for bumping....

[#8]

One last bump...can anyone help, please?

[astarodubov · Accepted solution]

Hello, Keith!

You should use this set analysis in all functions: {<MonthDate = {"<=$(=max(MonthDate))"}>}

And the whole expression will be:

count({<MonthDate = {"<=$(=max(MonthDate))"}>}  DISTINCT

    if(

        ConfirmationDate<=Aggr(NODISTINCT

        max({<MonthDate = {"<=$(=max(MonthDate))"}>} Date), YearDate, MonthDate)

        and ( DeliveryDate>Aggr(NODISTINCT

        max({<MonthDate = {"<=$(=max(MonthDate))"}>} Date), YearDate, MonthDate) or DeliveryDateIsNullYN = 'Y' )

    , OrderNumber

    )

   )

I wish it help you.

[#10]

Dear Alex,

Thank you for your kind assistance and pointing me in the right direction.

Best regards,

Keith Watson