Lead product and accessory sales

[Noel88]

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.

[Noel88]

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).

[Astounding]

Is your data in a sensible order, such as by customer date? Or by date? We can sort it if need be, but it helps to know what we are starting with.

[Noel88]

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.

[PaigeMiller]

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.

[Noel88]

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

[Astounding]

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.

[Noel88]

Currently the existing analysis is only looking at the sales of the lead product. I am of the view that this is underestimating the value of selling this product and that the additional items purchased by these customers adds further value to selling it. Anecdotally I'm being told that customers who buy this product are going on to buy accessories and other items. Sometimes at the same time, sometimes coming back days later. The primary analysis is to first see if this is the case - to move it from anecdote to evidence. I'm simply after a table that shows me all sales for the customers who purchase this one item. No further analysis is needed at this stage as if the additional sales are minimal then we won't do further work on it.

[Madelyn_SAS]

Which version of Visual Analytics are you using? 

[Noel88]

8.5.1
V 03.05

[HunterT_SAS]

Visual Analytics is not really great for being able to take and store a value from one row (Lead = yes for example) and apply that to other rows in a single calculated item. If this doesn't need to be anything fancy then would you be able to get away with counts that then link to the detail table?

For instance I took your data, and I created a calculated item converts Lead from Yes/Missing to 1/0:

IF ( 'Lead product'n In ('yes') )
RETURN 1
ELSE 0

This is necessary to apply a filter a bit later.
Then I renamed Frequency to Sales Count since each row appears to be a Sale, and created this table:

Then add an aggregated filter against Lead Number to get it down to just the customers that bought leads:

This should give you a quick look at how many total Sales were for customers that also bought leads.

Then you can add a second detail table if you need to see the individual sales, and create a filter action from the Lead table to the Detail table:

[Noel88]

That looks to be a viable solution. The logic flows through nicely. Thanks very much. I'll test that out when I'm back at work. Very much appreciated.