0 Replies Latest reply: Dec 14, 2012 5:52 AM by Kristian König RSS

    searching for formula

    Kristian König

      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.