Timestamp comparison in the set expression by getting date only

[tommyl]

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:

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

[petter · Accepted solution]

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])

[UserID2626]

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)

[Anil_Babu_Samineni]

@tommyl  May be this also?

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

[tommyl]

Thank you for the response.