1 Reply Latest reply on Apr 5, 2018 8:22 AM by nikher verma

    Sum the amount for each salers of my companie between 2 dates

    Mathieu Amra

      Hello Everyone !

      I've been trying to figure out for 2 days now how to do that, but nothing appears to be work yet.

      It seems pretty simple though but really I can't lose anymore time on this.

      Could you guys help me ?

      Here is my issue:

       

      I have the name of the Sales Reps. of my company and I'd like to print only the best seller of each month.

      Here are my different dimensions:

      - Amount (which is the cost of each plan that each Sales Rep. sells)

      - OwnerId (Id of the Sales Rep.)

      - CloseDate : The day when the Sales Rep. closed the deal.

       

      Here is what I've done so far : I created a calculate Field

      WhoisthebestSalesRep define as following : "

      {FIXED [Owner ID] : SUM(if (

      MONTH([Close Date] ) = MONTH(TODAY() )

      AND YEAR([Close Date]) = YEAR((TODAY())) )

      THEN [Amount]  end )} = {fixed : MAX({FIXED [Owner ID]  :

      SUM(if MONTH([Close Date] ) = MONTH(TODAY() )

      AND YEAR([Close Date]) = YEAR((TODAY()))    

      THEN [Amount]

      end)})}

       

      I used WhoistheBestSeller as a filter, condition being equal to *True*

      Then I took the amount and i've put SUM(Amount) in Label and OwnerID in columns.

      What is my issue?

      I don't have the Sales Rep. of this month, I only have always the same result, with amount that differ from what I would expect.

       

      If I had a chance, in SQL, i would have done something like: "

      SELECT MAX(AmountTot), OwnerID FROM (

              SELECT SUM(amount) AS AmountTot, ownerID

              FROM

              Database

              WHERE MONTH(CloseDate) = MONTH(now() ) AND YEAR(CloseDate() ) = YEAR(now())

              group by ownerID ) a

      "

       

      Does that make sense so far?

      If anyone would have any idea or similar issue, it would be really helpful !

      I cannot really disclose raw data as this is sensitive information from my company, but if you need it, I could send you some false representative data to give more sense to my issue.

      Thanks a lot in advance.

      Screen Shot 2018-04-05 at 16.25.07.pngScreen Shot 2018-04-05 at 16.26.50.png