Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Date Range using formatting

Hello,

I am struggling with trying to write the correct formula for a date range in the current month.  If I write

Sum({$<[Episode.NonAdmitDate]={">=2018-10-01 <=2018-10-31"}>}[Episode.IsNonAdmit])

it works fine, but I want to automate the process so I don't have to adjust the timeline every month.  I'm sure I jacked up the syntax in this, and wondered if someone could give me a little nudge in the right direction.  This is what I am trying to do (and the date format in the field is YYYY-MM-DD):

Sum({$<[Episode.NonAdmitDate]={'$(>=floor(Date(Monthstart(Today(),'YYYY-MM-DD'))) <=floor(Monthend(Date(Today(),'YYYY-MM-DD'))))'}>}[Episode.IsNonAdmit])

I've tried using ' and " in the set, and I've tried to position the 'YYYY-MM-DD' in different places in the ()'s, but I just cant get it to work.

Any suggestions?  Thanks in advance for your help!

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Hi Jeshwanth,

Your syntax works!  I changed the Date(MonthStart to MonthStart(Date and the same for MonthEnd and it works fine:

Sum({<[Episode.NonAdmitDate]={">=$(=Floor(Monthstart(Date(Today(),'YYYY-MM-DD'))))<=$(=Floor(Monthend(Date(Today(),'YYYY-MM-DD'))))"}>}[Episode.IsNonAdmit])

I really appreciate your help.  This one will go into my notebook

Thanks again, and have a terrific weekend!

View solution in original post

3 Replies
Highlighted
Creator
Creator

Oops, and I fixed the Monthstart(Date order, and it still didn't work (tried it both ways)

Highlighted
Creator II
Creator II

Try the below expression.

Sum({<[Episode.NonAdmitDate]={">=$(=Floor(Date(MonthStart(Today(),'YYYY-MM-DD'))))<=$(=Floor(Date(MonthEnd(Today(),'YYYY-MM-DD'))))"}>}[Episode.IsNonAdmit])

Highlighted
Creator
Creator

Hi Jeshwanth,

Your syntax works!  I changed the Date(MonthStart to MonthStart(Date and the same for MonthEnd and it works fine:

Sum({<[Episode.NonAdmitDate]={">=$(=Floor(Monthstart(Date(Today(),'YYYY-MM-DD'))))<=$(=Floor(Monthend(Date(Today(),'YYYY-MM-DD'))))"}>}[Episode.IsNonAdmit])

I really appreciate your help.  This one will go into my notebook

Thanks again, and have a terrific weekend!

View solution in original post