topic Re: Contract Management in QlikView

Contract Management

Tue, 21 Aug 2012 18:47:04 GMT

Hi everybody! I'm looking for some help with contract dates...in detail i'm looking for a script that shows me the contracts that I have to review in three months from the "today" date (when you open the document).

The script would show me how many contracts I have to read within this three months.

The values of the fields are dates in format DD/MM/YYY.

Thanks!!

Re: Contract Management

Tue, 21 Aug 2012 19:03:38 GMT

You could try a Flag field like this:

if(Date(YourContractRenewalField,'MM/DD/YYYY')-Today()<=90,'Y','N') as Flag

This will flag the ones that are less than 90 days away.

Re: Contract Management

Wed, 22 Aug 2012 08:56:07 GMT

Hi Rebeca, many thanks for your answer.

It would be great if you can help me a litle bit more...

I was wondering how I can do the following:

· Mark with the flag those between 90 and 180 days, this have to exclude those values under 90 and above 180

· Count those flag market to know how many contracts are going to expire in the following three months, between 3 and 6 and from 6 to 9, always excluding those out of the margin limits.

Many many thanks in advance

Re: Contract Management

Wed, 22 Aug 2012 13:20:55 GMT

I suggest adjusting the Flag field to the following:

if(Date(Date,'MM/DD/YYYY')-Today()<=90,'0-3 Months',if(Date(Date,'MM/DD/YYYY')-Today()>90 and Date(Date,'MM/DD/YYYY')-Today()<=180,'3-6 Months',if(Date(Date,'MM/DD/YYYY')-Today()>180 and Date(Date,'MM/DD/YYYY')-Today()<=270,'6-9 Months',if(Date(Date,'MM/DD/YYYY')-Today()>270,'9 Months+')))) as Flag

Then, you can build a Straight Table with 'Flag' as the Dimension and Count(Flag) as the Expression. It will look like this:

Hope this helps!

Re: Contract Management

Wed, 22 Aug 2012 13:25:16 GMT

The red 'Date' fields in that script is where you will put in your ContractRenewalDate field. Sorry, should have made that more clear.