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)
13 Replies
neelamsaroha157
Specialist II
Specialist II

Hi, Try the validation a table with all the fields as dimension + 2 of the variables that you have created in the dimension and count(Attendance Identifier) as your expression and check which part of the expression is failing to calculate the values.

qliklizzy
Creator II
Creator II
Author

Hi

 

I have managed to get the data to display in a table: 54 is the correct answer.

countif.png

I've realized, metric ID LI01 is my live current data. LI02 is my performance data including historic.

however I still don't get it right in the text box -  a count if statement actually works better than set analysis in the text box? -

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

I get 52  🙂

 

qliklizzy
Creator II
Creator II
Author

I get 54

I meant 54!  it is right 😉

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