Counting valid / invalid contracts, based on dates

[Anonymous]

Hi all,

I have a question about counting valid contracts and invalid contracts.

A contract can start every half month, so I would like to count the number of valid / invalid contracts on every day / week / month.

I read something about the interval function, but I am not sure on how to this. Could anyone of you please help me with this?

Attached you find an example of my data.

Kind regards,

Henco                   

[swuehl]

Hi henco,

I added a day timeline for some period of time (1000 days) to your script and a simple chart for valid contracts.

Invalid contracts should be ease then.

Do you have something like this in mind?

Regards,

Stefan

Edit:

Added invalid contracts and nicer timeline

[Anonymous]

By doing so, I've got troubles with the performance because it has to chech every single date.

Does anyone have ideas on how to do this?

Thanks.

Kind regards,

Henco

[swuehl · Accepted solution]

Hi henco,

it depends in what granularity you want to see the data.

You mentioned that contracts start / end only at start of month or in the middle.

So you could aggregate your data for example to week level without missing an event.

I think this should improve the performance.

Regards,

Stefan

Nachricht geändert durch swuehl

[Anonymous]

Right now I aggregate the data to month level, but unfortunately the performance is really bad.

The expression is now:

count(distinct(if((ConStartYearMonth <= CalYearMonth) and (ConEndYearMonth >= CalYearMonth), %CONTRACT_ID)))

In the script I made from the date a YearMonth.

Any suggestions?

Cheers,
Henco

[swuehl]

Hi Henco ,

do you use text format for YearMonth?

Then performance of count distinct is not good.

It is more performant to compare numbers, e.g. by using MonthStart(Date) to limit to Month and year.

Regards

Stefan