Wanting to see sales for all sales people who sell a lead product to customers to assess the impact of accessory sales. The accessory sales may be same day or later date.
Each sale has it's own line with a yes column next to the lead product item number but blank for everything else. Each line has a customer number.
I'm trying to get therefore
all sales to a customer but only if they had bought the lead product with the customer number being identified by the lead product column has a yes in it. The accessory sales are blank for the same column.
I was trying to do something like:
if lead product column = yes then return customer number
If customer number in above then return all sales (all lines which have that customer number) to those customer numbers
but can't get anything to work. Any help would be appreciated.
I can't change the data tableas I do not have access to do so I can only create a solution in VIYA.
Have worked a partial solution by creating a list of all the customer names filtered by yes column and used that list to click on individual customers which looks at, in a table showing all sales, to see what else they purchased.
If I could restrict the second table to only customers in the first list this would allow me to use totals, etc in a useful way.
(I'm away for a week now so no hurry for any thoughts).
Sales date is in there but not needed for this part as I will use that to determine new or existing customer i.e. sales before lead item purchase equals existing customer, no sales before lead item purchase new customer.
Show us a portion of your data following these examples and instructions. Show us the desired results.
Show us your code and EXPLAIN the problem you are having. Don't say "it doesn't work" without further explanation.
I won't be back at work for a week but have mocked this up in excel at home. This would be the input. The output would be the bolded lines. Obviously there would be other columns with sales person etc but this is the essential data to get from A to B. We can identify the customers we want by the yes. The second step is the one I don't know how to get - all sales for the customer numbers that have a yes in the lead product column.
I tried using an if then statement to get the customer numbers eg if lead product in "yes" then return customer number but couldn't get this to accept even when I changed the customer number to a category instead off a number. I'm starting to learn some coding but am definitely not yet competent and only have access to SAS VIYA report building functions i.e. not able to change data tables themselves.
Need to either include only the bolded sales or exclude the unbolded ones.
Customer number | Sales number | Lead product |
806185884 | 352642 | yes |
509804152 | 352643 | |
882331076 | 352644 | |
921338798 | 352645 | |
806185884 | 352646 | |
397206925 | 352647 | |
306612296 | 352648 | yes |
306612296 | 352649 | |
306612296 | 352650 | |
146638411 | 352651 | |
672286336 | 352652 | |
806185884 | 352653 | |
484973075 | 352654 | |
871998797 | 352655 | |
782777515 | 352656 | |
879936527 | 352657 | |
390223057 | 352658 | |
964182594 | 352659 | |
694348333 | 352660 | |
552739316 | 352661 | yes |
214321825 | 352662 | yes |
269519214 | 352663 | |
529652473 | 352664 | |
876472785 | 352665 | |
913604067 | 352666 | |
806185884 | 352667 | |
214321825 | 352668 | |
950587169 | 352669 | |
380356878 | 352670 | |
134078895 | 352671 | yes |
415736504 | 352672 | |
158368579 | 352673 | |
552739316 | 352674 | |
438888072 | 352675 | |
973265855 | 352676 |
This is a good start, spelling out the input. It looks like your data is sorted by sales_number which is helpful to know. However, it is a mistake to try to guide what the steps should be. Programmers on this forum are much more experienced and can guide you as to a good method to get the final result you want. In the same way that you are showing the input data, it would help if you could show the analysis you would like to perform once you have selected the right data points.
Which version of Visual Analytics are you using?
-------------------------------------------------------------------------
Four tips to remember when you contact SAS Technical Support
Creating Beautiful Reports
SAS Visual Analytics Learning Center
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.