load , If you have a sale in the Current Month and there were no sales in the last 4 months and it is greater than 10

[Ribeiro]

I need to create a flag when loading

If you have a sale in the current month, and have not had a sale in the last 4 months, and the Quantity of Pieces is Greater than 10.


Condition I found in the set analysis

if(Column(1) = 0 AND Column(2) = 0 AND Column(3) = 0 AND Column(4) = 0 AND Column(4) = 0 AND Column(5) > 0 AND Column(6) > 10 ,'Sem_VND _4Mes',Column(5))

Cod	jan 2023	fev 2023	mar 2023	abr 2023	mai 2023	Pç Mai 2023	Reativação
	Column(1)	Column(2)	Column(3)	Column(4)	Column(5)	Column(6)
192730	0,00	572,75	1.631,92	0,00	2.791,52	23	0,00
183252	1.111,43	15.712,93	33.359,23	38.992,79	2.778,84	26	0,00
609606	0,00	0,00	0,00	0,00	2.776,48	19	2.776,48
63808	4.340,28	5.727,48	3.293,81	2.145,60	2.328,99	17	0,00
14835	2.805,20	3.233,52	3.541,86	3.164,94	1.542,78	11	0,00
210731	0,00	0,00	0,00	0,00	1.525,56	23	1.525,56
56839	557,60	2.811,85	13,56	0,00	1.513,35	10	0,00
196260	0,00	1.225,42	881,04	1.600,24	1.426,42	8	0,00
87674	331,20	3,60	0,00	0,00	1.395,84	17	0,00
180073	535,80	1.080,32	974,55	548,40	1.368,60	4	0,00
12667	3.494,84	644,40	1.792,20	360,60	1.165,20	13	0,00
38516	0,00	0,00	0,00	0,00	1.080,25	5	0,00

desired solution

Cod	jan 2023	fev 2023	mar 2023	abr 2023	mai 2023	Pç Mai 2023	Reativação
	Column(1)	Column(2)	Column(3)	Column(4)	Column(5)	Column(6)
609606	0,00	0,00	0,00	0,00	2.776,48	19	2.776,48
210731	0,00	0,00	0,00	0,00	1.525,56	23	1.525,56

[Kushal_Chawda · Accepted solution]

@Ribeiro  try below

Data:
LOAD
    Cod,
    "Date",
    "ORDER",
    Sales
FROM Source;

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;

last_4_month_cod:
load Distinct Cod as last_4_months_code
Resident Data
Where Floor("Date")>= Floor(monthstart(addmonths('$(vMaxDate)',-4))) and
      Floor("Date")<= Floor(monthend(addmonths('$(vMaxDate)',-1))); 

latest_month_cust:
Load Distinct Cod as latest_month_cod,
              Date
Resident Data
where Floor("Date")>= Floor(MonthStart('$(vMaxDate)'));

Left Join(Data)
Load latest_month_cod as Cod,
     Date,
     1 as last_4_month_no_sale_flag
Resident latest_month_cust
where not Exists(last_4_months_code,latest_month_cod);

Drop Tables latest_month_cust,last_4_month_cod;

 Use below set expression to include Quantity condition

=sum({<Cod={"=sum({<last_4_month_no_sale_flag={1}>}Quantity)>10"}>}Sales)

[Ribeiro]

thank you for your skill and patience