Hello people!
I have a table with the following fields:
CD_CONTRACT | DATE_START | DATE_END
and a filter by DATE.
I need to create a table by year(DATE) with the count of contracts that are valid in each DATE.
I've tried this:
///////
=count(
{<
DATE_START = {"<=$(=max(date(DATE)))"},
DATE_END = {">=$(=min(date(DATE)))"}
>}
distinct CD_CONTRACT)
///////
and this:
///////
=aggr(
count(
{<
DATE_START = {"<=$(=max(date(DATE)))"},
DATE_END = {">=$(=min(date(DATE)))"}
>}
distinct CD_CONTRACT)
, DATE)
///////
But the count is always the total count, it doesn´t aggregate by DATE.
I know that one option is to create a table with CD_CONTRACT and a line for each date where the contract was valid. But I have a lot of contracts and very big valid periods, so it´s not an option because of very low performance
Can anybody help me with some idea please?
Thank you very much in advance