YTD Sales of selected persons

[Anonymous]

Hi,

I am trying to build a table showing year-to-date total sales amount of each salesperson who are still with the company.  Does anyone know how to do it on Table object (not in data load editor)?   Thanks.

Data table:

Month	Salesperson	Salesperson Status	Monthly Sales Records
Jan	A	With the company	Record 1 :   500 Record 2 :   1000
Jan	B	With the company	Record 1 :  600 Record 2 :  800
Jan	C	With the company	Record 1:   301 Record 2 :   450
Feb	A	Left the company	NIL
Feb	B	With the company	Record 1 :  600 Record 2 :  700
Feb	C	With the company	Record 1 :  450 Record 2 :  650

Expected Table object (as of Feb) :

Salesperson           Year-to-date Sales

B                             2700

C                             1851

Total                       4551

[sunny_talwar]

A single cell of your table include two values? Record1 and Record2? or is this in different rows?

[Anonymous]

In the data model,  record 1 and 2 are representing different product, thus they are in different rows.  Take the first row of the table to rewrite as below:

Month       Salesperson              Salesperson Status                   Sales                Product

Jan               A                                With the company                 500                    T-shirt

Jan               A                                With the company                 1000                  Jean

[rahulpawarb]

Hello Simon,

I have considered that you have Date field to track the sales transaction. I have prepared below logic to populate YTD Sales per Sales Rep (First Add Sales Person dimension and then add below expression).

Sum({<Year=, Quarter=, Month=, Date={">=$(=YearStart(Max(Date))) <=$(=Max(Date))"}>}Sales)

Hope this will be helpful.

Regards!

Rahul

[sunny_talwar]

So since A left the company in Feb, you won't show any sales?

May be like this

If(not WildMatch(Only({<MonthYear= {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>}[Salesperson Status]), '*Left*'), Sum({<MonthYear= {"$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year, Date>} Sales)

I am assuming that you have create a MonthYear field in the script using a logic like this

LOAD Date(TempDate) as Date

          Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

          ...

[Anonymous]

Thank you for your help.

Master Calendar is already built and the YTD sale is successfully calculated based on the srcipt provided.  However, those  " left the company"  salespersons still listed in the report.  When I tried to just list out those still-with-company salespersons by using their "status" at the latest month, the  YTD sale figures changed back to that single month's total.    Is there any way to avoid it?

[sunny_talwar]

Would you be able to share a sample to show the issue?

[Anonymous]

In the attached example, I tried to show the latest Active Salesperson only by using some MTD/YTD flag.  Thanks.

[Anonymous]

You are using MTD Flag in the expression which is changing the value to month level. You should have used YTD =1 to get the YTD value according to your logic.

[Anonymous]

Thanks Shiva.

When YTD is used, the Inactive salesperson is on then on the list.  I need those Inactive salesperson off the list while showing YTD figures.