SALES Problem

[#1]

Hi,

Please have a look at the QV example attached. What I need is the field in red to show the sales amount with costs. This should be 10.201,20 € and now shows 0 because of the credit note. Problem is that I have two invoices. One with costs related and one without (invoice # 1). Invoice # 1 without costs has a credit note related. I am not able to leave this invoice out of it.

I am not sure if the code is correct nor how to solve the issue. Thanks in advance for your help. Let me knwo if you need any more info.

Regards,Sjoerd

[#2]

You need something in the dataset to identify an invoice that you don't want to count into that expression.

To get the answer you want, you could simply use:

sum({<[Invoice #] = {2}>}if(COSTE0<>'0',Amount))- sum({<[Invoice #] = {2}>}[Amount Credit note])

But that probably won't work for all cases, so that's why you need a logical way to exclude invoice #1. Is it the credit note that requires it to be excluded?

[#3]

Problem here is the expression formula you have written is not in accordance to your business logic.

For us to comment on that, can you explain a bit on what you want to achieve with 'Sales with Costs'

referring to your data shown below.

[#4]

Thanks for the reply. I need to explain a bit more what I need. I need an analysis on all invoices that have a cost related. One table is invoice header. Another is an excel file with invoice # and the cost related to this invoice. In this table most of the invoices have a related cost but not all (as they have not been analysed yet). I have to exclude the ones without cost from the analisys. The third table (credit note) causes problems when the credit note relates to an invoice with no cost related. The logic works for most cases but not for this particular example. So what I am trying to do is to filter out all invoices without a cost related to them and when there is a credit note related to one of those invoices without cost then this shouldn´t be accounted either (see red colored field in example).

[eiconsulting]

I wonder if I got it right.

In my opinion if you want to include only the invoices that have a record in the "cost table" you should use the CostTable as main and LEFT JOIN the table where you have the amounts. The same you could do with the table with the credit notes.

In this way you have a list of invoice # with cost and only those specific invoices get a billed amount or even a credit note amount.

I mean something like:

<code/>

CostYES:
LOAD
[Invoice #],
Cost, YOU STILL WANT THIS FIELD OR IT ONLY SERVES FOR THE CALCULATION BELOW?
if(len(Cost) = 0 or Cost='A' or Cost='a' or Cost='0', '0', Cost) as COSTE0
FROM (biff, embedded labels, table is Sheet3$); //data from Excelfile

left join LOAD
[Invoice #],
Cliente,
Amount
FROM (biff, embedded labels, table is Sheet1$); //data is from ODBC

left join load
[Invoice #],
[Credit note],
[Amount Credit note]
FROM (biff, embedded labels, table is Sheet2$); //data is from ODBC

</code>

Should you want to get a full information set within the file and THEN exclude all the invoices with no associated cost you could just filter on COST0.

I could not make real tests without rebuilding the samples. Let's see first if this comment can be of any help.

Flavio

[#6]

Hi I tried your suggestion and it exlcudes data that I also need. What I need is what you mention afterwards:

"Should you want to get a full information set within the file and THEN exclude all the invoices with no associated cost you could just filter on COST0."

Can you explain a bit on how to "just filter on Cost0", because that is what is failing in my data. If I use the filter it works fine but I can´t get it to work without the filter in the table in the example.

[#7 · Accepted solution]

The expression you have written is sum(if(COSTE0<>'0',Amount))- sum([Amount Credit note]) This filters the Amount based on COSTE0, but doesn't filter the [Amount Credit Note]. So why don't you filter [Amount Credit Note] as well. That is make the expression like this sum(if(COSTE0<>'0',Amount))- sum(if(COSTE0>0,[Amount Credit note],0)) I'm not sure whether this is what you want. just see whether this solves your issue. Note that it is '>' sign not '<>'

[#8]

Thanks Richy. This works!

Thank you all for your help.

Regards,

Sjoerd