topic Re: Expression Help in QlikView

Expression Help

abhaysingh — Thu, 19 Nov 2015 07:02:01 GMT

hi ,

i am using date range selection and its working but facing one issue

below is my expression

avg({< Timestamp = {'>=$(=Date(vStartDate)) <=$(=Date(vMinDate))'} >}Temperature)

let say i have select vStartDate = 01-10-2015

and vMinDate = 09-10-2015

with my expression i m getting data 01-10-2015 to 08-10-2015, why i m not getting data for 09-10-2015 ?

pls suggest

Re: Expression Help

tamilarasu — Thu, 19 Nov 2015 07:07:25 GMT

Hi Abhay,

Your expression looks fine. Could you do a test by copy paste the below in text box and see. What value the variable return. I guess, minimum date will be the problem.

=Date(vMinDate)

Re: Expression Help

abhaysingh — Thu, 19 Nov 2015 07:14:26 GMT

yes i have checked, in Text Box it is giving me selected MinDate

Re: Expression Help

abhaysingh — Thu, 19 Nov 2015 07:16:40 GMT

PFA,test app

Re: Expression Help

tamilarasu — Thu, 19 Nov 2015 07:41:36 GMT

Your date format seems wrong. In list box, I can see same date replicated many times. Your date field contains date and time together.

Change the load script as below.

LOAD

AssetInstanceId,

Sensor_ID,

date(floor(Timestamp)) as Timestamp,

[Min-Max °C],

MasterLinkDate,

Temperature

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Expression Help

abhaysingh — Thu, 19 Nov 2015 08:08:56 GMT

Actually there is a timestamp with each date that's why there are multiple date for same day

Re: Expression Help

Mark_Little — Thu, 19 Nov 2015 08:14:26 GMT

Hi,

I think that will be the problem.

You are not comparing like fir like. So i think you will either have to add a time stamp to the vairable, or take the date from Timestamp field Like

date(floor(Timestamp)) as Date

Then use just the date

Mark

Re: Expression Help

tamilarasu — Thu, 19 Nov 2015 08:18:31 GMT

You are using the Timstamp field in you set analysis. You need to change field name as I mentioned.

Re: Expression Help

vincent_ardiet — Thu, 19 Nov 2015 08:45:53 GMT

Use daystart function:

avg({< Timestamp = {'>=$(=DayStart(Date(vStartDate))) <$(=DayEnd(Date(vMinDate)))'} >}Temperature)