Show Last Years Sales on New Employee

[Emir_Peker]

Hello everyone,

I encountered a problem in our company's sales reports. In general, the sales representatives of our business partners we work with are changed every year and our reports contain comparisons with last year. When the sales representative for the previous year changes, the turnover of last year is 0 but it shows the turnover of the new year, or other way around. How can I show the turnover of last year in the new sales representative?

I thought of using IF condition but I didn't know how to do it. Therefore, I request your support. Hope, I made myself clear.

[Emir_Peker]

I guess no one has a solution nor has no clue like me to solve this 😞

[tresesco]

---

@Emir_Peker wrote:

I guess no one has a solution nor has no clue like me to solve this 😞

---

That is one possibility but not the only one. If you have not any reply for long helping - that could also mean that - your post did not provide enough information for people out here. 

Try to explain your issue with sample data and/or sample app. I would try to make people's life easy who I am seeking help from by providing enough details. And that way interestingly, I would help myself get the solution faster. 

[Emir_Peker]

You're also right, but at least someone could have said "can you provide more information?" instead of being silent... Still thank you for your reply and I will try to make myself clear if my post didn't make any sense.

I prepared a TESTDATA to check it out. I will try to explain as simple as possible. Alex and Matt are our sale representatives.

Alex used to work with B in 2019. Alex is still working with A (2019 and 2020), and he is working with D in 2020. Matt is working with B in 2020 and for him to predict what the sales are going to be he needs to see value of 2019 on him instead of seeing 0.

Matt used to work with D in 2019. Matt is still working with C (2019 and 2020), and he is working with A in 2020. Alexis working with D in 2020 and for him to predict what the sales are going to be he needs to see value of 2019 on him instead of seeing 0.

So I want to show those values from previous year for the new representative. I hope I made myself clear. English is not my primary language so I am trying.

[tresesco]

• What if you have more than two years data, are you looking to get only the max(year)-1 data replaced by other representative's data?
• Is it always yearly data or it could be more granular? 

Do you want this in UI or script? 

[Emir_Peker]

I have measures for Quarters, Months and Year ... like you said max(year)-1.

It is a yearly data.

Script would be better in my opinion.

[tresesco]

PFA

Temp:
LOAD *,
	InvoiceDate & BusinessPartner as KEY
INLINE [
InvoiceDate,SaleRepresentative,BusinessPartner,Total
2019,Alex,A,100
2019,Alex,B,200
2019,Matt,C,200
2019,Matt,D,200
2020,Alex,A,100
2020,Matt,B,300
2020,Matt,C,250
2020,Alex,D,500
];

t1:
Load Max(InvoiceDate) as MaxYear Resident Temp;
Let vLastYear=Num(Peek('MaxYear'))-1;
Drop table t1;

Left Join (Temp)
Load 
	(InvoiceDate+1) & BusinessPartner as KEY,
    Total as LastYear_Total
Resident Temp Where InvoiceDate=$(vLastYear);  

[Emir_Peker]

It shows the same but I might be doing something wrong. Also another question, what if there is another invoice but representative is null ... It will get that null value tho right?

[tresesco]

I made only change in 2019 calculation, check that. Rest you could. And yes, no sales representative cases would also be considered.

[Emir_Peker]

If I add the LASTYEAR_TOTAL and remove 2019, it shows as below.

If you could check my load edit, there is an uncommented text. That way, I did it but I need to figure out isnull. Maybe use LEN and TRIM to figure it out?