Measuring net valid contracts started vs ended- Help

[#1]

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

[maxgro]

maybe this?

[MarcoWedel · Accepted solution]

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

[#4]

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);

[#5]

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

[MarcoWedel]

You're welcome.

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

thanks

regards

Marco

[#7]

will do, thanks.