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

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.

• ###### 1. Re: Sum the amount for each salers of my companie between 2 dates

Hi Mathieu Amra ,

Instead of doing that way  ,try this ::

create a field [cal1]

{Fixed[OwnerId]:sum[Amount]}

Drag this field to dimension and then create your viz using cal1 , OwnerId, Amount,Date .Use filter on cal1 and put top1 in filter .

If have any issue please attach your sample data or twbx file .

-Nikher