Filter On Computational Fields

[Colgan]

Hi Guys,

I'm pretty sure the answer is no but sure I'll ask anyway, can you filter on a computational field? I'm using 'DATEDIFF' to get the number of days between two dates. I'd like to set up a view and filter on the computed result(negative days). So a filter on the field as 'Starts with' a '-'.

Cheers

Saoirse

[Andrew.Shaw]

Hello Saoirse,

This is possible. The way I did it was to ensure that the result was a number and then I added a filter displaying values "less than" 0.

Best regards,

[MMcCabe]

Hi Andrew,

Thanks so much for the quick response. I need to change my syntax to cast the date difference as an integer so. I'm getting an error with the syntax below:

CAST(DATEDIFF('DAY', CONFIRMED_DELIVERY_DATE, SHIPPED_DATE)AS INTEGER)

Can you identify where I'm going wrong?

Cheers

Saoirse

[Andrew.Shaw · Accepted solution]

Hello Saoirse,

Here's an example that manages both negative and positive results:

(CASE WHEN DATE3 > DATE1 THEN 1 ELSE -1 END) * to_number(to_char((DATE1 - DATE3),'dd'),'99')

Best regards,

[Colgan]

That's fantastic Andrew. I have my integers now.

Thanks so much for your assistance.

Saoirse