topic Re: calculate date for active contracts in QlikView

calculate date for active contracts

Tue, 05 May 2015 11:00:24 GMT

I have worked with Qlickview for only a few days and now have the following problem:

I have two date fields in my address database, VertragBeginn (ContractBegin) and VertragEnde (ContractEnd). If an entry has no date VertragEnde or if the date is set but in the future, the entry has an active contract.

I want to create a diagram showing for each month in the last five years the number of active contracts in that month.

How can I extract the number of active contracts from my adress database for all required months with a single SQL -query?

Can I show the result directly in a diagram or do I have to create a separate table with the columns “month” and “number of active contracts”, so that I can use these columns as dimensions of the diagram?

Any help will be appreciated

Vini

Re: calculate date for active contracts

maxgro — Tue, 05 May 2015 14:40:01 GMT

PFA

Re: calculate date for active contracts

Fri, 08 May 2015 16:27:44 GMT

Hallo Massimo,

thank you for the example, can you tell me what you mean with c_x_ym

Vinzenz

Re: calculate date for active contracts

maxgro — Fri, 08 May 2015 17:44:21 GMT

just a name for the table

contract by year month

Re: calculate date for active contracts

rwunderlich — Fri, 08 May 2015 18:21:03 GMT

You may also want to check out this example that uses IntervalMatch to assign a day to each contract. That gives you the flexibility to count by day, month, whatever.

Qlikview Cookbook: Count Days In A Transaction Using Intervalmatch http://qlikviewcookbook.com/recipes/download-info/count-days-in-a-transaction-using-intervalmatch/

-Rob

http://masterssummit.com

http://robwunderlich.com