Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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