Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@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 try below if you have measure
Sum({<Date={">=$(=monthstart(max(Date)))<=$(=monthend(max(Date)))"},Cod={"=count(distinct Cod)= count(Cod)"}>}Sales)
My idea is something to load. Do you have any suggestions.
Is there a way to create flag, load with drop
🤔
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);
@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)')));
Thank you for your patience and expertise.