New Customers by Invoice Date

[renjithk]

Hi All,

i have a data model like customer category, customer code, invoice date, amount, profit.

Want to create a pivot table with the new customers in every month with Sales and Profit.

That is if a customer is not present in the entire database and first invoice date in 2018 Jan, its a new customer in 2018 jan and need the sales and profit of such customer. Similar for the Other Months.

[rubenmarin]

Hi, this can be easier with a little preparation in script.

- From invoice date group the values by year and month, this can be done using monthstart:

Date(MonthStart([invoice date])) as YearMonth

- Create a table with first month of each customer and a flag for first sale:

FirstSaleTable:

LOAD [customer code], Date(Min(YearMonth)) as YearMonth, 1 as FirstMonthSale Resident OriginalDataTableName Group by [customer code];

- Join this table with the original table

Left Join (OriginalDataTableName) LOAD * Resident FirstSaleTable;

- Delete FirstSaleTable:

DROP Table FirstSaleTable

Then you can use set analysis to load first month sales:

- Sum({<FirstMonthSale={1}>} amount)

- Sum({<FirstMonthSale={1}>} profit)

[Brett_Bleess]

Renjith, did Ruben's post help you with things?  If so, do not forget to come back and use the Accept as Solution button on his post to give him credit for the help and let others know his suggestions worked.  If you are still working upon things, leave us an update, so we can try to get you further information.

Regards,
Brett