Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qliklizzy
Creator II
Creator II

text box set analysis date time between two dates

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 🙂

 

Labels (5)
1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
Specialist II

=count(if(DateArrival=today()-1 and MetricID='LI02' and TriageCategory='Majors',AttendanceIdentifier))  

This should work same as above -

Count({<MetricID={'LI02'}, TriageCategory={'Majors'}, DateArrival={$(=Today()-1)}>}AttendanceIdentifier)

 

and instead of using Today-1, I would create a variable vPriorDate as =Date(Today()-1, 'SameFormatAsYourDateArrivalField' and use below expression - 

Count({<MetricID={'LI02'}, TriageCategory={'Majors'}, DateArrival={$(vPriorDate )}>}AttendanceIdentifier)

View solution in original post

13 Replies
Vegar
MVP
MVP

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
qliklizzy
Creator II
Creator II
Author

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
Creator II
Creator II
Author

qv datetime.png

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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 )