topic Counting valid / invalid contracts, based on dates in QlikView

Counting valid / invalid contracts, based on dates

Anonymous — Wed, 29 Jun 2011 15:19:24 GMT

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

Re: Counting valid / invalid contracts, based on dates

swuehl — Wed, 29 Jun 2011 15:40:39 GMT

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

Counting valid / invalid contracts, based on dates

Anonymous — Tue, 05 Jul 2011 16:05:10 GMT

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

Re: Counting valid / invalid contracts, based on dates

swuehl — Tue, 05 Jul 2011 16:53:08 GMT

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

Counting valid / invalid contracts, based on dates

Anonymous — Thu, 07 Jul 2011 08:12:29 GMT

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

Re: Counting valid / invalid contracts, based on dates

swuehl — Thu, 07 Jul 2011 13:03:04 GMT

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