Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

salleninsd
New Contributor III

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
salleninsd
New Contributor III

Re: Date Range using formatting

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!

3 Replies
salleninsd
New Contributor III

Re: Date Range using formatting

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

Highlighted
jeshwanth19
Contributor II

Re: Date Range using formatting

Try the below expression.

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

salleninsd
New Contributor III

Re: Date Range using formatting

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!