Customer bought this month and never bought before.

[Ribeiro]

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

[Kushal_Chawda]

@Ribeiro  try below if you have measure

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

[Ribeiro]

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

Is there a way to create flag, load with drop

 🤔

[Chanty4u]

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);

[Kushal_Chawda · Accepted solution]

@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)')));

[Ribeiro]

Thank you for your patience and expertise.