Calculated number in filter takes forever to run. Entering number is fast.

[JMoney RRD]

Hi,

I have a report that runs with a filter of around 90 days ago. Depending how the filter is setup it's fast or really, really slow. Problem is the fast way means changing the number manually whereas the really slow one with auto-calculate it.

So the filter looks like this, our system stores dates as numbers like yymmdd so unfortunately can't change that.

[Ship date] > yymmdd

Writing it like this with an actual number runs fast, like 3 minutes:
[Ship date] > 180731

Writing it like this is really, really slow. Like hours long slow:
[Ship date] > (extract(year,_add_days(current_date,-90))-2000)*10000+extract(month,_add_days(current_date,-90))*100+extract(day,_add_days(current_date,90))

That calculation ends up with a number for 90 days ago in the YYMMDD format. Not sure how else i could convert a date 90 days ago to yymmdd aside from that calculation.

What's weird to me is doing it with an = instead of a > in that formula and it runs fast again, even though I'd assume it would calculate the same amount of times.

Being able to have the date calculate on based on current date would be great so we don't have to change the number every few weeks but i'm stumped as to how to do so it runs quick again.

Thanks!

[CognosPaul]

I suspect it's an issue with an index somewhere. Is Ship date a calculated field?

In the meantime try this:

Code Select Expand

[Ship date]>#substitute('a20','','a'+timestampMask(_add_days($current_timestamp,-90),'yyyymmdd'))#

Timestamp mask doesn't support yymmdd so we have to cheat a bit. Concat a at the beginning and substitute out a20. It's ugly but it works.

[JMoney RRD]

Yes thank you this worked to make it run much quicker!