How to filter using date range like last year/quarter/month/week/day
Answered
I've a big table that I want to filter using date range - in a dynamic way
That is the filter should display in rows:
----
All
Today
Week
Month
Quarter
Year
----
With each these values it should return the corresponding date value calculated dynamically using the current date.
Is it possible to do? If yes, how? Please suggest.
Thanks,
Girish
BMC Softeare.
The same question
Hi Girish,
Thanks for reaching out. You might be able to accomplish this creating Pre-Defined Formula type Calculated Fields:
There are numerous types of Functions. You choose one and set the Date section to use one of your fields and as you can see, you can choose Current Date and set an operator to choose Days, Weeks, Months, etc. You'd just create a separate Calculated Field for each. You can also script Custom Functions that would then show up here. It may also be possible to accomplish this by drafting Freehand SQL statements as well. Essentially you'd be scripting calculated fields that specify certain date ranges for whatever user prompt filter value you'd be inputting. Neither of these would display in Rows of the same column however.
To have each of these value display in individual Rows, it may be possible this can be accomplished by scripting a Freehand SQL statement, the exact scripting of which falls outside the scope of Support and would lie more in the realm of Consulting services. Either way, I can't say for sure whether this is possible as Support doesn't typically handle data-specific report creation questions such as these. Regardless, I suspect that if possible, the solution would lie in either scripting Freehand SQL statements or Custom Functions.
The only other pointers I can give is to test queries directly in your RDBMS first to see if the query works in the first place. The only that came to mind after doing some research was using datepart and/or datename, like so:
But as you can see, this still returns the values in separate columns. Hopefully this gives some idea to work with at least!
Regards,
Mike
Hi Girish,
Thanks for reaching out. You might be able to accomplish this creating Pre-Defined Formula type Calculated Fields:
There are numerous types of Functions. You choose one and set the Date section to use one of your fields and as you can see, you can choose Current Date and set an operator to choose Days, Weeks, Months, etc. You'd just create a separate Calculated Field for each. You can also script Custom Functions that would then show up here. It may also be possible to accomplish this by drafting Freehand SQL statements as well. Essentially you'd be scripting calculated fields that specify certain date ranges for whatever user prompt filter value you'd be inputting. Neither of these would display in Rows of the same column however.
To have each of these value display in individual Rows, it may be possible this can be accomplished by scripting a Freehand SQL statement, the exact scripting of which falls outside the scope of Support and would lie more in the realm of Consulting services. Either way, I can't say for sure whether this is possible as Support doesn't typically handle data-specific report creation questions such as these. Regardless, I suspect that if possible, the solution would lie in either scripting Freehand SQL statements or Custom Functions.
The only other pointers I can give is to test queries directly in your RDBMS first to see if the query works in the first place. The only that came to mind after doing some research was using datepart and/or datename, like so:
But as you can see, this still returns the values in separate columns. Hopefully this gives some idea to work with at least!
Regards,
Mike
Hi Girish,
I just wanted to check in and see if this information found you okay.
Regards,
Mike
Hi Girish,
I just wanted to check in and see if this information found you okay.
Regards,
Mike
Hi Girish,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Hi Girish,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Replies have been locked on this page!