topic Re: text box set analysis date time between two dates in QlikView

text box set analysis date time between two dates

qliklizzy — Sat, 16 Nov 2024 01:22:44 GMT

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 🙂

Re: text box set analysis date time between two dates

Vegar — Fri, 31 Jan 2020 14:02:04 GMT

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.

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 14:28:47 GMT

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)

Re: text box set analysis date time between two dates

Kushal_Chawda — Fri, 31 Jan 2020 14:41:54 GMT

what is the format of ArrivalDateTime ?

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 14:44:21 GMT

thanks

Re: text box set analysis date time between two dates

Kushal_Chawda — Fri, 31 Jan 2020 14:49:46 GMT

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 )

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 14:54:08 GMT

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

Re: text box set analysis date time between two dates

Kushal_Chawda — Fri, 31 Jan 2020 14:58:01 GMT

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

Re: text box set analysis date time between two dates

Kushal_Chawda — Fri, 31 Jan 2020 15:03:59 GMT

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 )

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 16:06:29 GMT

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 )

Re: text box set analysis date time between two dates

neelamsaroha157 — Fri, 31 Jan 2020 16:13:19 GMT

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.

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 17:08:40 GMT

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

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 🙂

Re: text box set analysis date time between two dates

qliklizzy — Fri, 31 Jan 2020 17:12:33 GMT

I get 54

I meant 54! it is right 😉

Re: text box set analysis date time between two dates

neelamsaroha157 — Mon, 03 Feb 2020 21:45:54 GMT

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

Re: text box set analysis date time between two dates

AshrafTai — Mon, 24 Jun 2024 15:36:03 GMT

This helped a lot.