topic Re: Measuring net valid contracts started vs ended- Help in QlikView

Measuring net valid contracts started vs ended- Help

Sat, 31 Jan 2015 15:14:13 GMT

I am having problems trying to create chart of net contracts started vs ended. I have tried using insurance contract example for the link below and using the interval match and cant figure it out. The loop creates millions of records. I am trying to right the script so when I make one chart of total contracts shipped less expired year.

Attached is the qvw with example data.

Generating Missing Data In QlikView

Re: Measuring net valid contracts started vs ended- Help

maxgro — Sat, 31 Jan 2015 18:12:27 GMT

maybe this?

Re: Measuring net valid contracts started vs ended- Help

MarcoWedel — Sun, 01 Feb 2015 00:25:59 GMT

Hi,

one solution could be:

DistinctContracts:
LOAD *,
     Year(Date) as Shipyear,
     Date as Birthdate,
     Year(EndDate) as ExpYear,
     -(EndDate<Today()) as ContractEnded;    
LOAD ContractId as ContractIdShipped,
     Date(Min(StartDate)) as Date,
     Date(Max(EndDate)) as EndDate
Resident Contracts
Group By ContractId;

DROP Table Contracts;

MasterCalendar:
LOAD *,
     Day(CanDate) as Day,
     WeekDay(CanDate) as WeekDay,
     Week(CanDate) as Week,
     WeekName(CanDate) as WeekName,
     Month(CanDate) as Month,
     MonthName(CanDate) as MonthName,
     Dual('Q'&Ceil(Month(CanDate)/3),Ceil(Month(CanDate)/3)) as Quarter,
     QuarterName(CanDate) as QuarterName,
     Year(CanDate) as Year,
     WeekYear(CanDate) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as CanDate
While MinDate+IterNo()-1<=MaxDate;
LOAD RangeMin(Min(Date),Min(EndDate)) as MinDate,
     RangeMax(Max(Date),Max(EndDate)) as MaxDate
Resident DistinctContracts;

tabLink:
CrossTable(DateType, CanDate)
LOAD ContractIdShipped,
     Date as Shipped,
     EndDate as Expired
Resident DistinctContracts;

hope this helps

regards

Marco

Re: Measuring net valid contracts started vs ended- Help

Sun, 01 Feb 2015 13:03:19 GMT

This is great. Thanks for the help. I haven't used CrossTable's so far and I see how useful it can be. Your solution is more akin to my original link above.

Marco's solution also works using a date island:

Island:

load

Distinct ExpYear as Year

Resident

DistinctContracts;

Concatenate (Island)

load

Distinct Shipyear as Year

Resident

DistinctContracts

Where

not Exists (Year, Shipyear);

Re: Measuring net valid contracts started vs ended- Help

Sun, 01 Feb 2015 13:04:43 GMT

Thanks I meant Massimo in my orginal reply. Much thanks....

Re: Measuring net valid contracts started vs ended- Help

MarcoWedel — Sun, 01 Feb 2015 14:03:42 GMT

You're welcome.

Please mark one of the answers correct that answered your question.

thanks

regards

Marco

Re: Measuring net valid contracts started vs ended- Help

Sun, 01 Feb 2015 16:04:24 GMT

will do, thanks.