searching for formula

Fri, 14 Dec 2012 10:52:40 GMT

Good day everybody,

i have 2 small questions and i hope i get an quick answer

I have an Chart(straight table) with Customer number, Date from, date to.

1.) The calculation i need calculates the profit(Lineamount) for faktures(SALESID) with more then total Lineamount 300 (SALESID >300 Lineamount).

My formula:

sum(if(Datum_fakture>[Datum poč. ugovora] and Datum_fakture<[Kraj ugovora], LINEAMOUNT)) = Means: Calculate profit if fakture Date is between (date1 and Date 2). I think i am missing an "and" but i do not now the fornula to resolve my problem.

The table i get the Profit from is build like this:

Cust nr - SALES ID - Artik nr - Lineamount

1 555 xxxx 20

1 555 xxxy 20

1 555 yyyy 30

Means one SALESID is present more times(depends on number of sold articles)

2.) Ignore returned SALESID. I Have an formula that counts SALESIDs(Distinct) but i dont know the formula to ignore SALESIDs from returned faktures.

Ex.: November 2012 - buyer x, 3 faktures(SALESID), December 2012 - buyer x - cumulative 5 faktures BUT 1 fakture is returned from November so it has to be 4!

The only way i see if something returned is a negative value on LINEAMOUNT but SalesID is the same.

Any help would be appreciated. 1) is the bigger problem

Thank you.

topic searching for formula in QlikView

searching for formula