Conditional output to Table Box... or to smth else?

[#1]

I have a table containing insurance contracts data like the following:

CompanyID	CompanyName	ContractID	StartDate	EndDate
1	A	1	01.01.2013	31.12.2013
1	A	2	01.01.2014	31.12.2014
2	B	3	01.01.2013	31.12.2013
2	B	4	01.04.2014	31.03.2015
3	C	5	01.01.2014	31.12.2014

As a result in Table box I need CompanyID and CompanyName of the companies that do not have an active contract on the desired date (received from input box or just now() ). So for today (30.01.2014) I should have Table Box containing only {2,B}. I have no idea, how to make a self join with conditions and dynamically apply it to the Table Box. Or I should use some other sheet object instead? Thanks!

[maxgro · Accepted solution]

try this

count({$ <StartDate=-{"<=$(=floor(Today()))"},EndDate={">=$(=floor(Today()))"}>} StartDate)

see attachment

[#3]

Thanks! As far as I understood, such conditions can not be applied to TextBox, only to chart?

[maxgro]

Textbox

you can use expression in textbox (in caption, .....) and

=count({$ <StartDate=-{"<=$(=floor(Today()))"},EndDate={">=$(=floor(Today()))"}>} StartDate)

is an expression

So the answer is: you can, yust create a textbox and use the expression as text

Tablebox

No, because you can't use expression in tablebox

[#5]

You're right, I meant TableBox. Friday... ;-). Thanks again!