text box set analysis date time between two dates

[qliklizzy]

Hi

I have searched and tired various set analysis to bring back data for yesterday but I get an error, even though it says expression ok?

=count({$ {< ArrivalDateTime = {">=$ (= date(today()-1)) <=$(=date(today()))"}

,MetricID={'LI01'}
,TriageCategory={'Majors'} >}
distinct AttendanceIdentifier )

all I want to do is show in a text box the number of arrivals for yesterday.  arrival date time is a datetime field.

thanks

for any help 🙂

[Vegar]

I think your expression looks good as long as your MerticID and TriageCetegory values are spelled correctly.  

Your ArrivalDateTime needs to have a numeric value in order for your modifier to work. If you add the ArrivalDateTime to an list box, are the values left or right aligned as default? If they are left aligned then it's an indication that it's not numeric.

[qliklizzy]

Hi

thanks, yes I thought it should work too. yes fields are all correct and arrival date time is date time right aligned.

I have this expression in another box to pick up today so far. which works.  which makes me think it is something to do with today()

=count({<MetricID={'LI02'},
TriageCategory={'Majors'},
ArrivalDateTime={">=$(=only(daystart(UpdatedDateTime)))"}>} distinct AttendanceIdentifier)

[Kushal_Chawda]

what is the format of ArrivalDateTime ?

[qliklizzy]

thanks

[Kushal_Chawda]

as your Date field is in timestamp format, try using timestamp function instead Date in set analysis

You can also try giving actual format in timestamp function within set analysis

=count({$ {< ArrivalDateTime = {">=$ (= timestamp(today()-1)) <=$(=timestamp(today()))"}

,MetricID={'LI01'}
,TriageCategory={'Majors'} >}
distinct AttendanceIdentifier )

[qliklizzy]

nope still the same message in the text box.  expression still says ok .

error: error in expression: '}' expected

is there a way to change the arrival date time to a date?  doesn't like that in the expression when I have tried

[Kushal_Chawda]

which expression you are using? There is a syntax error. That is why it's not working.

If you want to convert the DateArrivalTime to date format. Just create a separate field in script

LOAD *,

            date(floor(DateArrivalTime)) as DateArrival

Now you can use your same expression with DateArrival

[Kushal_Chawda]

try this, make sure that the timestamp format should match with ArrivalDateTime field format

=count($ {< ArrivalDateTime = {">=$ (= timestamp(today()-1,'MM/DD/YYYY hh:mm:ss')) <=$(=timestamp(today(),'MM/DD/YYYY hh:mm:ss'))"},MetricID={'LI01'},TriageCategory={'Majors'} >}distinct AttendanceIdentifier )

[qliklizzy]

HI

Sorry my phone interrupted me,

that expression just gives me an error...

I have added a date field in now, in my sql which feeds in.  and I get a number - zero! - but its wrong 😞

il have to see what its doing now..

=count( {< date = {">=$ = date(today()-1)) <=$=date(today())"}
,MetricID={'LI01'},
TriageCategory={'Majors'} >}
distinct AttendanceIdentifier )