how to avoid repeated values in a maxdate to -30 days

[ramrongala]

Hi expertise,

MY TABLE:

data:
load *Inline [
Ticket_ID,Serial_No,Date
1,123,01/01/2021
2,234,10/01/2021
3,123,25/01/2021
4,234,27/02/2021
5, 456,28/02/2021

];

Requirement: need to display the count of ticket repeated  for Serial_No between max(Date) to -30 days

Output should be: here max date is 28/02/2021, last 30 days no repeated serial no so output will be "0" .

i have tried below code(backend):

data:
load *Inline [
Ticket_ID,Serial_No,Date
1,123,01/01/2021
2,234,10/01/2021
3,123,25/01/2021
4,234,27/02/2021
5, 456,28/02/2021

];
NoConcatenate

data1:
load *,
Autonumber(Ticket_ID,Serial_No) as [Sequence No],
date(Date) as updated_Date
Resident data;
Drop Table data;

Expression: count( {<[Sequence No]-={1},Date={">$(=Date(Max(updated_Date)-30))<=$(=Max(updated_Date))"}>} Ticket_ID) 

My output: 

here serial no 234 also should be skip, but its appear 

please help me how to archive this.

[Anil_Babu_Samineni · Accepted solution]

Perhaps this in design?

Count({<Date={">=$(=Date(AddMonths(Max(Date), -1)))<=$(=Date(Max(Date)))"},
Ticket_ID={"=Count({<Date={>=$(=Date(AddMonths(Max(Date), -1)))<=$(=Date(Max(Date)))}>} TOTAL <Serial_No> Serial_No)>1"}>} Serial_No)

[ramrongala]

Hi Anil,

Thank you for providing solution , its working i have done some changes as per my requirement.

but reload time very huge(around 3 hours takes time) when i used above code(see me code above).

so any idea how we can reduce that load time or any other way to archive this.   

[Anil_Babu_Samineni]

I would recommend to create new ticket for this with more information.