Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
try this
count({$ <StartDate=-{"<=$(=floor(Today()))"},EndDate={">=$(=floor(Today()))"}>} StartDate)
see attachment
try this
count({$ <StartDate=-{"<=$(=floor(Today()))"},EndDate={">=$(=floor(Today()))"}>} StartDate)
see attachment
Thanks! As far as I understood, such conditions can not be applied to TextBox, only to chart?
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
You're right, I meant TableBox. Friday... ;-). Thanks again!