Challenge to create a back-order App

[#1]

Hi community,

I was wondering if somebody  can help me out.

I've been trying to create a BackOrder report without any luck.

For those how are not familiar with the term BO, Place an order for (a product) that is temporarily out of stock

Background, I have 3 different tables in the system with the following information

1) Sales Lines (Sales detail by Item, Customer, Sales Order, Sales amount, sales Quantity, Line No(Invoice Line Number), etc)

2) Sales Header (Sales Order Number)

3) Item master (Brand and Item number)

These 3 tables Joins naturally, very straight forward.

Since there is not a field for invoiced status I created one with the help of the following IF:

   IF( [Invoiced $]=0,'BackOrder', if(Quantity-[Original quantity] >0, 'BackOrder', 'Invoiced')) AS "Invoiced Status"

With the previous IF statement I got 3 different scenarios:

A)

For the SOR013762 there is only one BO for Line No 50000, PO Orig $ = $1,963.50 (minus) Invoiced $= $0, Dif$ (Backorder) -$1,963.50

B)

SOR013782 BO for line 20000, PO Orig $ = $5,625.90 (minus) Invoiced $= $2,768.30, Dif$ (Backorder) -$2,857.60

THE CHALLENGE....

C)

The last SOR shows a TOTAL BO of $12,022.79 but that amount is not actually the correct BO amount, if we look closely the SOR013847 it is related to 3 different Invoices,  what happened is that when the SOR lines were invoiced, some of the lines were missing and not invoiced on the first or the second invoice, but the third. So the IF statement duplicate, triplicate the BO amount or showed a BO that does not exist.

1) Number 1 Is showing two BO's for a total of $1,535.52 line 10000 , but line 10000 was already invoiced so there is $0  BO

2) Number 2 Is showing 3 BO's  for a total of $1,230.96 line 60000 but just only one is correct $410.32

Attached is very short version of my model with an example of the app an a DB in excel.

Thank you very much!

Have a great week!

[swuehl]

I think you need to aggregate your invoiced qty in the script, see attached.

[#3]

Hi Swuehl, thank you very much for your prompt response, with the approach you are suggesting still have the duplicate or triplicate info in the model. What I am looking is to prevent loading those values and not showing them at all, obviously just keeping the correct one.

Thanks

!

[swuehl]

You can do a subsequent resident load of your sales table and filter the records with a WHERE clause.

I am not sure which records do you define as correct, maybe all

WHERE not ("Invoiced Status" = 'Invoiced' and Quantity = 0);

(note the I applied the status on an aggregated level, hence you need to look at my sample, not at your first sample images).

If this is not what you want, you just need to adapt the WHERE clause to your required logic.

[#5]

Sorry for not clarifying what is correct... What rules, first the invoiced, but in some instances, where the PO Orig Qty has a value and the invoiced Qty is either "0" or "Less than" the PO Orig Qty, the backorder rules

What is happening is that billing department and shipping forgot to invoice lines on the first invoice SIO13503, for example if the invoice had 10 lines and on the first invoice were only invoiced 4 and 6 lines were missing, those missing lines need to be invoiced in a second invoice or sometimes in a third, like in this case, or until all the lines are invoiced. How our system works is that the info is pulled from the Customer PO, so if a second, third, fourth,etc.. invoice is done, ALL the LINES of that Customer PO will be shown on the invoice not matter if they were previously invoiced or not.

Hope I am clear enough.

Again thank you very much for your time and effort I greatly appreciate it!

Have a great day!

[swuehl · Accepted solution]

Ok, but in what information are you still interested in?

Can we remove the information of single invoice, then i would suggest to aggregate on Line No level.

Something like attached.

edit: replaced the attachment, corrected the PO Orig $ calculation.

[#7]

I am interested to show both, cases and USD  not shipped (BO) as well as cases and USD invoiced .

How can I aggregate on Line No level??

Thank you very much.

[swuehl]

I think this analysis is now possible using my latest sample file.

Are you able to open these files and check the results?

[#9]

I've just checked the result and that is exactly what I wanted to achieve, you are awesomeeeeeeeeeeeeeeeee 

Can you please explain me  how you  did it

I never used the only function before what is that for?

Also it is possible to get the invoice number back?

Many, many thanks

[swuehl]

I guess it might be a little too much new stuff at once. I would suggest that you get a good book (e.g. the QlikView 11 for Developers book) and start a more step by step learning.

There is also a must-read, the Qlik design blog series, one of the postings being about Only() function:

The Only Function

I am a bit unsure how to best present the single invoices, but still do the aggregation of quantities and $ right.

One possible solution would be to create a table with a order line granularity for the facts that don't change across invoices.

See attached sample.

It still shows the facts per invoice and line, but take a look at the partial sums, the aggregation on higher level should now be correct.