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.