Webi Query Filter - Between Formula

[bekart]

Hello Community,

I am trying to find a way to filter on my 'CreationDate' field to only return entries created from seven days ago (from current date) to current date. I've tried creating a 'detail' object with no luck. I have converted my 'CreationDate' field to a date field using the 'ToDate' function (varCreationDate), and then applying : =[varCreationDate] Between (RelativeDate(CurrentDate(); -7); CurrentDate())

with many different syntax and  iterations ran through Galileo AI with no luck. This seems like it should be a fairly intuitive, I'm hoping it is an easy fix! Thanks as always!!!

[bekart]

in report you can build variable which will check the days between and flag them.
For example:
=If(DaysBetween([CREATIONDATE];CurrentDate())>=0 And DaysBetween([CREATIONDATE];CurrentDate())<=7) Then "show" Else "hide"

[bekart]

hi, does your query is based on SQL?
If so use filter on query you can use FHSQL statement in WHERE clause: 

CREATIONDATE>=sysdate()-7

 not sure about the date format so bear in mind that trunc may be necessary. 

[JG432]

I unfortunately do not have access to use a SQL query. That 'would' be ideal

[JG432]

@bekart, is there a way to only return the values in which the 'creationdates' are in between the present day and seven days ago? Thank you for your reply as well

[JG432]

Basically, is there a way to set the between dates on a date query filter to be dynamic?

[JG432]

Your earlier post: =If(DaysBetween([CREATIONDATE];CurrentDate())>=0 And DaysBetween([CREATIONDATE];CurrentDate())<=7) Then "show" Else "hide"

worked out! I just had to set the filter after that to exclude "hide"

Thank you!!!

[bekart]

happy to hear that - good practise is to limit data on query level but in your case you are not able to do so. So filtering data on report level is the correct solution.