Basic date input query

[#1]

Hi all,

I have just started playing around with the Personal Edition connecting to an ODBC database and have a basic query on allowing the end-user to search a table based on dates.

For example, I have a table (Accidents) which contains Home & Date fields. I would like the user to be able to see how many Accidents each Home had in a given week (Monday-Sunday). Within the Edit Script command, I can use commands (i.e. where Accidate >= 40469 and Acciddate <= 40475) etc) but the intended end-user would not have access to Edit Script .

After searching on here, I have tried various options such as using the slider/calendar object and entering two Excel tables [startdate] and [enddate] but cannot make it work.

Would appreciate some basic advice please.

Thanks,

Matt

[hopkinsc]

Ok, like i said, the trigger expressions on the variables i dont understand, maybe someone else here can tell us what it does exactly?

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

The expression that is added to your results box should just display results which fall within your chosen date range.

[hopkinsc]

Remember to make sure that you change my examples to match your variable names EXACTLY as it is case sensitive...

[#4]

hopkinsc - i am confused as to the two separate Expressions that you displayed above:

Where should the following expression be entered?

=if(vStartDate > 0,'>=' & date(vStartDate, '$(DateFormat)')) & if(vEndDate >0,'<=' & date(vEndDate,'$(DateFormat)'))

The last expression you highlighted (the Sum({<Trading Date one - am I correct in thinking this should be in the Edit Expression field on the table that displays the result to the end user?

I am still struggling with the expression I would like for my particular count: ie. count(acciddate) where the accidents recorded between vStartdate and vEnddate. I have tried adapting your Sum expression for my Count to no avail.

Thanks again

[hopkinsc]

Hi, that expression goes as a variable trigger.

Document Properties>>Triggers>> variable Event triggers. select your variable, click on add actions (on input), click add, select in field, and enter:

date in the field box

and the expression in the string box.

do this for both variables..

[#6]

Thanks Hopkinsc, it looks like everything is fine up to the expression stage in my final box that the user sees. I keep getting error in expression when I am trying to enter the count accident formula between the two variable dates - any ideas? I'll keep trying.

[hopkinsc]

Hi, what is the expreesion you are writing?

[#8]

Hi hopkinsc,

I have been trying variations on the following (as previously mentioned, I am very new to this so still finding my way around!) but the best I can do is just return the total number.

COUNT ({< acciddate = {">=$(=Date(vStartdate))"}*{"<=$(=Date(vEnddate))"} >}acciddate)

Basically, I just want to count every accident in a given time period as chosen by the user using the 2 calendar options [startdate] & [enddate}, connected to variables vStartdate & vEnddate respectively.

Thanks

Matt

[hopkinsc]

Hi Matt,

Could you post a sample so i can see your tables names and field names??

[#10]

The table I am looking at is called accidents which contains a field acciddate which is just a date field that an accident occured (and another field which gives me the Home name from another table but that is fine)

I wish to extract the number of accidents that each Home has recorded by counting how many times the acciddate appears.

I have a calendar object [startdate] which is linked to a variable vStartdate . Also have another calendar object [enddate] which is linked to vEnddate. Both variables have the following search string:

=if(vStartdate > 0,'>=' & date(vStartdate, '$(DateFormat)')) & if(vEnddate >0,'<=' & date(vEnddate,'$(DateFormat)'))

I have a chart which I wish to display the total number of accidents for each Home in a given period by the user using the two calendar objects to give a startdate and enddate and its the expression in this chart which I am struggling with to 'connect' to the two calendars (I am writing the expression for this count in Chart Properties/Expressions/Definition.

Previously, I have just used count(acciddate) to give a total for all Accidents by Home which worked fine but now I wish for the user to select variable dates using the calendars.

Thanks again.

Matt

[hopkinsc]

Hi Matt,

Strange as its working fine for me. I have just copied your expression into my document and changed the accidate to one of my fields and it worked. What version of QLIKVIEW are you using?