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.