Count the specific Sales of a specific Product and display it along side each Customer

[#1]

Hi guys,

I need to count the number of times each store bought a specific product. The problem is I need this info for each store. Therefore storing it in a table that includes every store so it links back to my Sales Table.

In order to get this info in a new table I'm thinking I need to do a resident load of my existing Sales Table. This table has CustomerCode and ProductCode as well as the NettSales as columns. I can already get the resident load to help me find stores that have sold the product, I just can't get it to to show me how many times the customer has bought it.

Ideally the result would be a separate column that has the number of times each CustomerCode has purchased the specified ProductCode.

Do I need to do a Group By, I have a feeling I do, however I can't get it to work that way.

Below is my code that allows me to find the Customers that have bought the product:

[SoldProd]:

Load Distinct

  [Customer_Code],

  if([Product_Code]="Widget1",'Bought','Didn't buy') as [Sold_W]

Resident [SALES];

Any help would be hugely appreciated.

Thanks

Message was edited by: Nicholas Child Added example, I need "Sold Air Product" text box (highlighted in red) to display 'Yes' or 'No' and filter the data accordingly.

[crusader_]

Hi,

Why don't you want to create a Chart with 2 Dimensions Store, Product and Expression count(purchId) ?

Regards,

Andrei

[#3]

Hi Andrei,

That would be a simple solution. Unfortunately I need to do this in the script load.

I need the numbers from the count to be selectable from a List Box (i.e. Customers that bought 1,2,3,4,5,6 times)

Thanks for your reply.

[#4]

Hi there,

I created a field called Flag on the back-end that counts how many products a particular customer has bought. Please check it out and see if it helps.

Thanks

[#5]

Hi Ajay,

Thank you so much for your detailed response. The .qvw was very helpful and confirmed I was on the right track. I'm still struggling to only count for a specific product. Any ideas? I have an if-statement trying to only include that Product but it fails to load giving an "Invalid Expression" error.

Thanks again!

[#6]

Can you attach a sample file if it isn't too big?

[crusader_]

Hi Nicholas,

I can assume your issue It could be connected with word "Didn't" inside your if statement.

LOAD Key,

     if(Count(Product_Code)=0,'Didn'&chr(39)&'t buy','Bought '&Count(Product)&' times') as Bought_flag

Residen't SALES

Group By Customer, Product;

Hope this helps.

Andrei

[#8]

Hi again,

Nope sorry, that Didn't was added when I made this post, it's not the issue I have.

However, your method of finding the stores that have or haven't bought has helped me too.

Thank you for your reply

[crusader_]

So, please attach your sample file, then I can help you more.

Andrei

[#10]

Hi Andrei,

See attached example in my original post.

I'm trying to filter the data based on whether a store bought a product or not. I can't get the highlighted List box to give me an option to select all stores that didn't buy.

Thanks for your help