Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Emir_Peker
Contributor III
Contributor III

Show Last Years Sales on New Employee

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_0-1588084076707.png

 

Labels (1)
1 Solution

Accepted Solutions
Emir_Peker
Contributor III
Contributor III
Author

I found the solution so thank you for your help.

View solution in original post

10 Replies
Emir_Peker
Contributor III
Contributor III
Author

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

tresesco
MVP
MVP


@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
Contributor III
Contributor III
Author

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
MVP
MVP

  • 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
Contributor III
Contributor III
Author

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
MVP
MVP

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
Contributor III
Contributor III
Author

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?

Emir_Peker_0-1589273523010.png

 

tresesco
MVP
MVP

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

Emir_Peker
Contributor III
Contributor III
Author

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

Emir_Peker_0-1589273962954.png

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?