topic Re: Customer bought this month and never bought before. in App Development

Customer bought this month and never bought before.

Ribeiro — Mon, 01 May 2023 22:20:02 GMT

I need to analyze check within my database. Being the customer made the first purchase this month.

ORDER	Cod	Date	month/year
48884	4	21/07/2021	jul 2021
49169	4	26/07/2021	jul 2021
46393	4	01/05/2023	mai 2023
49257	10	01/05/2023	mai 2023
132917	12	01/05/2023	mai 2023
35271	13	25/10/2020	out 2020
38558	14	28/12/2020	dez 2020
153812	13	01/05/2023	mai 2023
7967	14	01/05/2023	mai 2023

Customer 10 and 12 only, no sales in any other period

Only first sale in current month

attachment qvd

Expected outcome

ORDER	Cod	Date	month/year
49257	10	01/05/2023	mai 2023
132917	12	01/05/2023	mai 2023

Re: Customer bought this month and never bought before.

Kushal_Chawda — Mon, 01 May 2023 22:55:21 GMT

@Ribeiro try below if you have measure

Sum({<Date={">=$(=monthstart(max(Date)))<=$(=monthend(max(Date)))"},Cod={"=count(distinct Cod)= count(Cod)"}>}Sales)

Re: Customer bought this month and never bought before.

Ribeiro — Mon, 01 May 2023 23:35:52 GMT

My idea is something to load. Do you have any suggestions.


Is there a way to create flag, load with drop

🤔

Re: Customer bought this month and never bought before.

Chanty4u — Tue, 02 May 2023 02:52:35 GMT

Try this

Orders:

LOAD ORDER, Cod, Date, MonthYear(Date) as MonthYear

FROM [path\to\your\file.qvd]

(qvd);

FilteredOrders:

LOAD *

Resident Orders

Where MonthYear = MonthYear(FirstSortedValue(Date, Date, Asc, Cod)) and

Cod in

(SELECT Cod

FROM Orders

GROUP BY Cod

HAVING Count(DISTINCT MonthYear(Date

)) = 1);

STORE FilteredOrders INTO [path\to\output\file.qvd] (qvd);

Re: Customer bought this month and never bought before.

Kushal_Chawda — Tue, 02 May 2023 10:12:03 GMT

@Ribeiro try below

Orders: LOAD ORDER, Cod, Date, MonthYear FROM [path\to\your\data1.qvd](qvd); max_date: LOAD max(Date) as max_date; LOAD date(fieldvalue('Date',recno())) as Date autogenerate fieldvaluecount('Date'); let vMaxDate = peek('max_date'); drop table max_date; left join(Orders) LOAD Cod, 1 as new_customer_flag; where cnt_code=1 LOAD Cod, Count(Cod) as cnt_code resident Orders group by Cod; new_customers: noconcatenate LOAD * resident Orders where new_customer_flag=1 and (floor(Date)>= floor(monthstart('$(vMaxDate)')) and floor(Date)<= floor(monthend('$(vMaxDate)')));

Re: Customer bought this month and never bought before.

Ribeiro — Tue, 02 May 2023 14:06:18 GMT

Thank you for your patience and expertise.