topic Re: New Customers by Invoice Date in QlikView

New Customers by Invoice Date

renjithk — Sat, 16 Nov 2024 02:12:17 GMT

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.

Re: New Customers by Invoice Date

rubenmarin — Thu, 17 Oct 2019 15:38:29 GMT

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)

Re: New Customers by Invoice Date

Brett_Bleess — Wed, 23 Oct 2019 18:46:03 GMT

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