Set default value as this month

1

Hi everyone, I'm trying to set the date filter's default value to this month when the filter isn't chosen. Without input of Field Filter, where clause works fine, and shows correct (as expected) results, but once I put the filter into, it shows:
" No results!
My query:
Where DATE_TRUNC('month',CreatedAt) = DATE_TRUNC('month', [[ {{Selectmonth}}::date , ]] CURRENT_DATE() )
Any ideas what could be wrong, or this is a known issue and will not work as excepted?

Thank you in advance.

2

Hi,

My suggestion is write the query this way:

where cast(field.date as date) = [[{{data}}--]]cast(now() as date)

when you put information in optional filter, it input the '--' unabling this part of query cast(now() as date)

I hope it help you.

1 Like
3

I believe @bjg has the right idea here. I think using that "commenting out" pattern for your specific WHERE clause might look something like this:

where DATE_TRUNC('month', CreatedAt) = DATE_TRUNC('month', [[ {{Selectmonth}}::date) -- ]] CURRENT_DATE())

I could have messed the syntax up though. This looks like PostgreSQL, but I only have access to a MySQL instance so can't actually test it.

1 Like
4

I tried your solution but it occured error:

image
image1821×694 92.3 KB

Do you have any idea? :pensive:

5

Hi, I tried using your syntax but it occured error

image
image1835×699 90.7 KB

Do you have any idea to fix it? I'm using postgresql.
Thank you very much :heart:

6

Hmmm... It's a little hard to tell because the screenshot gets cut off, but it looks like you didn't include the closing parenthesis after {{Selectmonth}}::date. You need that to properly format the DATE_TRUNC call when there is a value provided for Selectmonth.

Here's the full line again:

where DATE_TRUNC('month', CreatedAt) = DATE_TRUNC('month', [[ {{Selectmonth}}::date) -- ]] CURRENT_DATE())

Note that you also need the closing parenthesis after CURRENT_DATE() for the cases where a Selectmonth value has not been provided.

1 Like
7

Thank you, @jamesbontempo. I figured it out. Can I ask you another question related to the complex default filter for the date range?

where 1=1 [[and {{CreatedAt}}]] [[and {{OrgId}}]] [[and {{SubCompanyId}}]] [[and  {{ProposerId}}]]  [[and {{departmentId}}]] 
      [[and {{ProposerName}}]] [[and {{departmentname}}]] [[and {{SelectedUserName}}]] [[and {{SelectedUserId}}]] [[and {{Flowtitle}}]]
  AND "public"."smart_formmodel"."CreatedAt" >= coalesce([[{{Startdate}}::date,]]  DATE_TRUNC('month', current_date)) AND
    "public"."smart_formmodel"."CreatedAt" <= coalesce([[{{EndDate}}::date,]] current_date )

It works fine in almost every case, but it doesn't work for pivot tables that can't use native SQL.
I'm trying to use field filter {{date_range}} instead of 2 single date filters like now. But I don't know how to set it as This month when nothing is chosen, it looks more complex than using 2 separate filters.
Do you have any idea for this case? Pls let me know

1 Like
8

@thanhxuan glad you figured it out!

This new one looks like it might take more time to figure out than I can devote right now. I think it might be a good idea to post it as a new topic/question. That way, someone else might see it & be able to provide an answer :+1:

1 Like
9

I agree this issue seems complex and might take some time to resolve. I'll post it as a new topic. Thanks for helping me @jamesbontempo :heartbeat: