how to skip some records based on condition in expression

[ramrongala]

Hi experts,

i have to skip the no of tickets(<0 and >40 days),below logic for
Formula= (visit start date -visit end date)

so if no of days below 0 and above 40 days need to skip those tickets.

entire logic should be = avg{<status code='Yes', Category= 'Sales',  (visit start date -visit end date)<0 and (visit start date -visit end date)>40>},Tickets)

but this columns coming from different source

[MayilVahanan · Accepted solution]

Hi @ramrongala 

Try like this, create a day difference in script

Interval(visit end date-visit start date,'d')  as DayDiff

In Frontend,

avg{<status code={'Yes'}, Category= {'Sales'},DayDiff= {"<0 >40">}Tickets)

or

If(Interval(visit end date-visit start date,'d') <0 and Interval(visit end date-visit start date,'d')  > 40, 1, 0) as Flag

avg{<status code={'Yes'}, Category= {'Sales'},Flag= {1}>}Tickets)

[PrashantSangle · Accepted solution]

I don't thinks so and clause will work. you should try with or clause.

modifying Mayil's solution

Interval(visit end date-visit start date,'d')  as DayDiff

In Frontend,

avg({<status code={'Yes'}, Category= {'Sales'},DayDiff={"<0"}> + <status code={'Yes'}, Category= {'Sales'},,DayDiff={">40"}>}Tickets)

or

If(Interval(visit end date-visit start date,'d') <0 or Interval(visit end date-visit start date,'d')  > 40, 1, 0) as Flag

avg{<status code={'Yes'}, Category= {'Sales'},Flag= {1}>}Tickets)

Regards,

[ramrongala]

Thank you , its working.

[ramrongala]

Thank you , its working.