Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
maybe this?
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
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);
Thanks I meant Massimo in my orginal reply. Much thanks....
You're welcome.
Please mark one of the answers correct that answered your question.
thanks
regards
Marco
will do, thanks.