Hello,
I posted a similar question a few months ago, but I ran into some limitations with the solution I was given so I decided to post again.
I was tasked with adding our sales rep’s monthly targets into one of my dashboards, so we could see if they’re on track to hit those targets. I used a calculated field, using the switch function, to get it done and it works well for the reps that have very lucrative areas and have sales each month, but it stops being useful for the ones that are trying to break into new markets, and thus some months maybe have no sales in a certain region. The problem arises where I try to add a target for one of our reps that didn’t have sales in one month - see example below.
We have thes sales:
| Sales Rep |
Year |
Month |
Sales |
| Rep1 |
2025 |
1 |
990 |
| Rep1 |
2025 |
3 |
750 |
| Rep1 |
2025 |
4 |
1400 |
| Rep1 |
2025 |
5 |
1100 |
| Rep1 |
2025 |
6 |
350 |
And these are the targets below:
| Sales Rep |
Year |
Month |
Target |
| Rep1 |
2025 |
1 |
1000 |
| Rep1 |
2025 |
2 |
1000 |
| Rep1 |
2025 |
3 |
1000 |
| Rep1 |
2025 |
4 |
1000 |
| Rep1 |
2025 |
5 |
1000 |
| Rep1 |
2025 |
6 |
1000 |
| Rep1 |
2025 |
7 |
1000 |
| Rep1 |
2025 |
8 |
1000 |
| Rep1 |
2025 |
9 |
1000 |
| Rep1 |
2025 |
10 |
1000 |
| Rep1 |
2025 |
11 |
1000 |
| Rep1 |
2025 |
12 |
1000 |
Now, in my head, when I do a left join, with the Targets table on the left side of it, the result should be:
| Sales Rep |
Year |
Month |
Target |
Sales |
| Rep1 |
2025 |
1 |
1000 |
990 |
| Rep1 |
2025 |
2 |
1000 |
null |
| Rep1 |
2025 |
3 |
1000 |
750 |
| Rep1 |
2025 |
4 |
1000 |
1400 |
| Rep1 |
2025 |
5 |
1000 |
1100 |
| Rep1 |
2025 |
6 |
1000 |
350 |
| Rep1 |
2025 |
7 |
1000 |
null |
| Rep1 |
2025 |
8 |
1000 |
null |
| Rep1 |
2025 |
9 |
1000 |
null |
| Rep1 |
2025 |
10 |
1000 |
null |
| Rep1 |
2025 |
11 |
1000 |
null |
| Rep1 |
2025 |
12 |
1000 |
null |
But instead, this is what I am left with after the join:
| Sales Rep |
Year |
Month |
Target |
Sales |
| Rep1 |
2025 |
1 |
1000 |
990 |
| Rep1 |
2025 |
3 |
1000 |
750 |
| Rep1 |
2025 |
4 |
1000 |
1400 |
| Rep1 |
2025 |
5 |
1000 |
1100 |
| Rep1 |
2025 |
6 |
1000 |
350 |
I believe the 2nd month should still show up, even with no sales in that month. I need it to show up so my total target for the year gets calculated correctly.
Please someone help me out with this, I’ve been struggling for about a week on and off and I can’t figure out how to do this. I am open to suggestions, if you guys have a better way of doing it.
Thank you!
-Alex
Ciao @SuciuAlex,
I think the issue is just cause you’re using a left join starting from the dataset which is missing some months of data.
Let’s suppose you have the Sales and the Target tables.
If Target table is the one that always have all the values for all the months, you should:
- Use the Target table as first dataset and do a Left Join to the Sales one
- Or use theSales as first dataset, and do a Right Join with Target one
Let me know if this helps,
best regards
Andrea
Hi Andrea,
This is what I was doing, I just worded it poorly it seems.
I am doing a Left join, with the Targets table (the one that has all the months) on the left side of it. The Sales table is on the right. They are also matched on the Sales Rep, Year and Month fields.
To add to this, regardless of which dataset I start with, the end result is the same. February is still missing. Is there something I’m doing wrong when displaying the data maybe?
Is your left join something like this?
Yup, exactly like this. Still not working as (I think) it should.
Hi @SuciuAlex
The devil is in the details. Can you share details if your dataset and the join for review?
Regards,
Giri
Hello,
For sure, here’s a screenshot of the join itself.
The “Targets” file has the targets for every rep, formatted like the screenshot below (I changed the name of the reps to Rep1 and Rep 2 so I don’t post their real name here).
And below is a screenshot of the Sales dataset itself. Each row is a sales order line, so it’s not aggreggated. Eveything I covered in red is a customer’s name, refference etc. that shouldn’t be public.
What am I doing wrong?
So Sales Team - Customer is nothing but the Agent with a different name. Is that right?
Yeah, they’re the same thing. Just has a fancier-sounding name in the Sales dataset
Hey @SuciuAlex, let’s do a recap.
Correct me if I’m wrong, but you have a dataset Sales with lot of different rows that can be referred to same Agent, Year, Month, while you have another dataset Targets where you have a single row for Agent, Year, Month and you want to merge the two info.
I’ve created these two fake datasets like yours:
Q1. Sounds all good also with fields data types and so on?
Then, starting from the Target dataset, I’ve then created a join with Sales like this:
Q2. This is exactly the same you’ve done, right?
Once applied, I have data like this:
- multiple rows for same Agent-Year-Month values
- null values for Targets where I do not have a single sales for Agent-Year-Month
Q3. This is what you expect as output when you do the join, but you’re seeing something different, right?
Q4. And this is likely and example of the output you’re trying to achieve, right?
Can you show us a screenshot of the data obtained in the Data Prep? Like the one before Q3?
Have you tried build a visualisation like the last one I’ve pasted? What’s the output?
Andrea
1 Like
Hello,
Q1. Yup, everything looks good so far.
Q2. Yes. Here is a screenshot, I’m doing it again right now, following your instructions.
Q3. This is what I am seeing here in Data prep, exactly as expected. Same as in your screenshot.
Q4. So apparently it now works. This is what I am seeing, exactly as expected and exactly as in your visual.
But you can see in the screenshot below - I did this yesterday, in the exact same way and posted the screenshots here yesterday. In the data prep window it looks exactly the same, and and the way it should (the same as the screenshot I posted in this comment), but when I try to make the same visual, this is what I am getting. What could cause this one to not work as intended? It seems like I just did the same thing twice and got different results…
Thank you for your help, I really appreciate it.
Hi Alex,
it’s difficult to say what was wrong. It really seems that you were already doing all the steps in the best way!
BTW, glad to see that everything is working now 
Let us know if the problem appears again and we’ll try to go deeper in the troubleshooting!
Andrea
1 Like
