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

# 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

• ###### Re: Best way to count "Order Bank" over time

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

Can you provide some sample data?

• ###### Re: Re: Best way to count "Order Bank" over time

Hi Manish,

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

• ###### Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Best way to count "Order Bank" over time

Keith,

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

• ###### Re: Re: Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Re: Best way to count "Order Bank" over time

Now i can see what you need

Should be easy

• ###### Re: Best way to count "Order Bank" over time

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.

• ###### Re: Re: Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Re: Best way to count "Order Bank" over time

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:

OrderNumber,

ConfirmationDate as Doc_date,

'Order' as type,

1 as Bank_counter,

Month(ConfirmationDate) As MonthDate,

Year(ConfirmationDate) As YearDate

resident transactions;

Concatenate

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

• ###### Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Best way to count "Order Bank" over time

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

• ###### Re: Re: Best way to count "Order Bank" over time

• ###### Re: Re: Re: Best way to count "Order Bank" over time

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.

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.

Keith

• ###### Re: Best way to count "Order Bank" over time

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

• ###### Re: Best way to count "Order Bank" over time

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

)

)

• ###### Re: Best way to count "Order Bank" over time

Dear Alex,

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

Best regards,

Keith Watson