Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
A single cell of your table include two values? Record1 and Record2? or is this in different rows?
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
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
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
...
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?
Would you be able to share a sample to show the issue?
In the attached example, I tried to show the latest Active Salesperson only by using some MTD/YTD flag. Thanks.
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.
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.