Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ramrongala
Contributor
Contributor

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

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: 

ramrongala_2-1616599601266.png

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

please help me how to archive this.

 

Labels (1)
2 Replies
Anil_Babu_Samineni

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ramrongala
Contributor
Contributor
Author

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.