Line matching issue

[#1]

Hi everyone

One of our companies operates with files. What this means is that they have a file per customer per shipment.

With each file there is a invoiced figure to the customer and a estimated cost of sales. The result then is a estimated gross margin for that file. Once we have received the actual invoice from our suppliers, we capture these actual cost of sales invoice against that file.

The file is then closed and we now have an actual gross margin/profit for that file.

The issue we currently have is that we have a lot of open files and the actual cost of sales invoice was captured all over the place and not allocated to the correct files etc. I would like to develop some sort of a Qlikview model which will help them to resolve these issues.

I love working on Excel and it is the easiest way for me to display the end result and what I need Qlikview to do.

In this file, you will see the data structure/lines of the file. Column A-G. In column G you will notice that I created a link field which is a concatenated value between File Number, Creditor Number & Posting Code. This is a unique key. In column I you will see that I removed the duplicated keys/links.

I column J & K it did a pure Sumif. In Column L I did a IF formula which is the answer I am looking for.

The reasoning behind the IF formula is that we need to establish what estimated cost of sales actually has actual cost of sales invoice posted against it. The issue we have is that the actual invoice and the estimated figure is recorded on different lines (different Transaction ID') which makes a simple IF formula complicated. That is why I created the key, so that we can actually match the estimate and actual. That is where the IF formula comes in. If a key/link has a actual amount, it must ignore the estimated amount, but if there is no actual, it must take the estimated amount. The answer marked in yellow must be the end result.

I need this formula to work on all dimensions, so the option of just summing the rows for a specific dimension is not a solution.

I have attached the excel example. Hope that this makes sense.

Thanks

[Anonymous]

Hi,

It does

A basic lead that will help you elaborate.

The chart function can be brought back into the script.

For more hints, let me know.

Cheers,

ANtoine

[#3]

Antoine, thanks for your reply, but this did not sort out my problem.

I have attached your example which I modified, but I still do not get to the answer as in the Excel sheet.

Cheers

Christo

[Anonymous]

Sorry Chris, I am troubleshooting a client application. Once finished, I'll have a deeper look.

[#5 · Accepted solution]

Attached with answer.

Please look at the back-end script and the expressions in the chart.

Thanks

AJ