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 Nicholas,

PFA qvw file.

This is not elegant solution but still.

Hope I understood you correctly.

Andrei

[#3]

I don't think I've explained myself very well. Your example works well however I need the data to show the stores sales of ALL products when I select Yes from Sold Air Products. I hope that make sense. So when I select "Yes" the table below shows sales for all stores and all products as long as that store has sold an "Air" product.

Hope this makes more sense. I'm sorry I have been so confusing.

To restate my problems:

I need to display the sales by store. The stores need to be segmented by whether they sold a specific product or not ("Air" in my examples) while still displaying the sales for ALL products. i.e "Air sellers" and "Non-Air Sellers"

I also need to be able to segment the stores by how many times they purchased the specific product ("Air" in my example). This should not limit the data by only "Air" products. i.e. Stores that "Bought Air Once, Twice etc"

Hope this makes sense.

[crusader_ · Accepted solution]

Hi, Nicholas

Please find attached the example.

Hope it helps.

Regards,

Andrei

[#5]

Thank you so much, I spent a few hours last night doing a very similar load using a few temporary tables.

I really appreciate your help with this. Well done

[crusader_]

Welcome.

Also to avoid Expression in "Sold Air" filter change last table like that:

T:

Right Join (Table)

LOAD

  Store,

  if(tmpF=0,'Never Sold','Sold') as FlagLast

Resident tmp;

cause I'm against using <Expressions> in Filters if it possible.