Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Timestamp comparison in the set expression by getting date only

Hello experts,

In the load editor i have these variables:

LET vActivityStart = Date( Monthstart( today() ) , 'YYYY-MM-DD');
LET vActivityEnd = Date( today() , 'YYYY-MM-DD' );

And in one of my tables i have this field:

Timestamp(createdat, 'YYYY-MM-DD hh:mm:ss[.fff]') as GMTcreatedat,

 

And in the chart editor i have these input boxes(attached capture.jpg) for vActivityStart and vActivityEnd variables:

Capture.JPG

In the set expression, i'd like to filter my data according to the "GMTcreatedat" field, by comparing this field's value with vActivityStart  and vActivityEnd. I wrote sth like:

Sum({$<GMTcreatedat={"<=(Timestamp$(vActivityStart), 'YYYY-MM-DD hh:mm:ss[.fff]')>=Timestamp($(vActivityStart),'YYYY-MM-DD hh:mm:ss[.fff]')"}>} [balance])

In order to sum balance of the activities which has timestamps between "activityStart" and end date in Time stamp format. 

But i cannot do it. Do you know how can i filter sum of values according to the user-input date values(like 2019-01-01) by comparing these dates with time stamp values(like 2019-01-01 12:00:14.) ?

 

Regards,

Tommy

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

I think this expression should work for you:

Sum({$<GMTcreatedat={"<=$(=Timestamp(vActivityEnd,'YYYY-MM-DD hh:mm:ss[.fff]'))>=$(=Timestamp(vActivityStart,'YYYY-MM-DD hh:mm:ss[.fff]'))"}>} [balance])

 

I think using Date() instead of Timestamp() would be better though (unless GMTcreatedat is in the full timestamp format):

Sum({$<GMTcreatedat={"<=$(=Date(vActivityEnd, 'YYYY-MM-DD'))>=$(=Date(vActivityStart,'YYYY-MM-DD '))"}>} [balance])

 

View solution in original post

4 Replies
Highlighted
MVP
MVP

I think this expression should work for you:

Sum({$<GMTcreatedat={"<=$(=Timestamp(vActivityEnd,'YYYY-MM-DD hh:mm:ss[.fff]'))>=$(=Timestamp(vActivityStart,'YYYY-MM-DD hh:mm:ss[.fff]'))"}>} [balance])

 

I think using Date() instead of Timestamp() would be better though (unless GMTcreatedat is in the full timestamp format):

Sum({$<GMTcreatedat={"<=$(=Date(vActivityEnd, 'YYYY-MM-DD'))>=$(=Date(vActivityStart,'YYYY-MM-DD '))"}>} [balance])

 

View solution in original post

Highlighted
Creator II
Creator II

Sum({$<GMTcreatedat={">=$(=Timestamp($(vActivityStart), 'YYYY-MM-DD hh:mm:ss[.fff]'))<=$(=Timestamp($(vActivityEnd),'YYYY-MM-DD hh:mm:ss[.fff]'))"}>} [balance])

Condition inside set expression should start with $(=expression)

Highlighted

@tommyl  May be this also?

Sum({$<GMTcreatedat={"=Date(GMTcreatedat, 'YYYY-MM-DD')>=$(vActivityStart) and Date(GMTcreatedat, 'YYYY-MM-DD')<=$(vActivityStart)"}>} [balance])

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Thank you for the response.