How to show NewSales bases on Date,Customer and Product?

[Anonymous]

Hi community,

i have got a problem that i'm not able to solve. After days of research i hope you can help me! I don't now if it is possible, but I'm sure if there is a way you will find it.

In our sales reporting I want to create a table that shows the "NewSales". A sale is a NewSale if a product has never been sold to a customer before (first occurance of customer AND product) . After the first sale of the product to this customer, all following sales of this product to the same customer are also NewSales for one year. Is the same product is sold to another customer, all sales are NewSales too for this customer. (for one year).

If i assume, that the sales in the table below are all sales ever made. Than the green rows should show the NewSales.

For Example:

     Record 7 is a NewSale

          because there is no record with Date <=31.03.2016 AND Customer = 2010076154 AND Product = C5303

     Record 8 is not a NewSale

          because Record 1 is in the selection <=30.04.2016 AND Customer = 2010076154 AND Product = C5062

I had the ideas of implementing a flag for NewSales in the Load or build a table with all NewSales via indirect set analysis. But all my attemps failed. In a "normal" programming language i could probably solve this with a nested loop.

I will attach the .qvw file with my test data. I hope you can help me!

Thank you.

[sunny_talwar · Accepted solution]

Check the attached

rawdata:

LOAD RowNo() as Record,

Date(MakeDate(Year, Month)) as Date,

*;

LOAD * INLINE [

    Month, Year, Customer, Salesman, Product, Sales

    01, 2012, 2010076154, Mueller, C5062, 6823.74

    09, 2014, 2050348638, Mueller, 989, 45.1

    04, 2016, 2010076154, Mueller, C5303, 6823.74

    04, 2016, 2050365821, Sommer, 991, 33

    01, 2017, 2050348638, Mueller, 985, 45.1

    02, 2017, 8170144016, Mueller, 135, 189

    03, 2017, 2010076154, Sommer, C5303, 6823.74

    04, 2017, 2010076154, Mueller, C5062, 6823.74

    04, 2017, 2010076154, Mueller, C5303, 6823.74

    04, 2017, 2050365821, Sommer, 991, 3

    05, 2017, 2050365821, Mueller, 989, 6

    09, 2017, 2050348638, Mueller, 989, 45.1

    09, 2017, 2050348638, Mueller, 985, 45.1

];

sales:

LOAD *,

If(Customer = Previous(Customer) and Product = Previous(Product), 1, 0) as Flag

Resident rawdata

Order By Customer, Product, Year, Month;

Left Join(sales)

LOAD Product,

Min(Date) as MinDate

Resident sales

Where Flag = 0

Group By Product;

FinalSales:

LOAD *,

If(Flag = 0 or MinDate > AddYears(Date, -1), 1, 0) as NewSales

Resident sales;

DROP table rawdata, sales;

[Anonymous]

Hey Sunny!

You made my day!

I just changed the join-part to

Left Join(sales)

LOAD Customer&'-'&Product as $Key,

Min(Date) as MinDate

Resident sales

Where Flag = 0

Group By Customer, Product;

Otherwise the MinDate would depend on the first occurance of the product only and not on the first occurance of cutomer AND product. This wasn't noticable with my original test data. I changed the test data and came to this adjustment.

Next week i will implement this to my application and I'm sure it will work fine.

Thanks a lot! & Greetings from Germany.