Matching multiple end dates interval and counting members.

[#1]

Hi,

I'm trying to calculate the nr of members at a certain point in time with different statuses. ACTIVE, FINISHED and so on. And also if they have a start date at a certain date that means the are counted as a NEW member. An EndDate means that the contract is finished.

A simple intervalmatch solves this but in this case there are different states which means different EndDates.

%Key	State	ContractStart	ContractTo	ContractTerm	NextPayment
123123	FINISHED	12/12/19	13/06/19		13/06/19
234234	FINISHED	12/12/19	13/06/19		13/06/19
234235	FINISHED	12/12/19	13/06/19		13/06/19
534534	FINISHED	12/12/19	13/06/19		13/06/19
345345	FINISHED	12/12/19	13/12/19		13/12/19
868585	FINISHED	12/12/19	13/12/19		13/12/19
234234	FINISHED	12/12/19	13/12/19		13/12/19
123123	TERMINATED	13/12/13	13/12/27	14/03/27
245364	ACTIVE	14/04/02		14/07/02	14/07/02
795305	TERMINATED	14/02/27		14/03/27	14/03/27

How can I match the ContractStart and the three different EndDates, ContractTo,ContractTerm and NextPayment and in any point in time be able to calculate the Active members, New members, Ended Members and so on?

Should I use slowly changing dimensions for this or is there another any other approach?

I tried setting a new EndDate with rules that said if is null(ContractTo,ContractTerm and so on in order to grab the most relevant EndDate and then do an interval match but I'm not getting the correct results.

Any help is appreciated.

[Gysbert_Wassenaar]

See attached qvw. Is that what you're looking for?

[#3]

Thanks Gysbert!

I've tried something similar but without the alt () function. I need all three end dates combined. ContractTo, ContractTerm and NextPayment. Is that possible?

I'll check with more data if this is a step in the right direction!

Thanks again!

[#4]

Been trying to get the last date with a nested if but I keep getting one or two rules wrong.

Rules are:

If there are dates available then this order should be the rule. The lowest available date should be the one picked:

ContractTerm,ContractTo, NextPayment.

If there are no dates available the opposite should rule.

So if ContractTo is null then ContractTerm, if ContractTerm is null then NextPayment.

      If(ContractTerm<=NextPayment,
  ContractTerm, if(isnull(ContractTo),NextPayment,
if(IsNull(NextPayment) and  (ContractTerm<=ContractTo) ,ContractTerm,
  if(IsNull(ContractTerm) or isnull(ContractTerm),ContractTo,
  // if(isnull(ContractTerm) or ContractTerm<=ContractTo ,if(isnull(ContractTerm),ContractTo,
if(isnull(ContractTo),ContractTerm, if(isnull(ContractTerm) or ContractTerm>=ContractTo ,
if(IsNull(ContractTerm) or isnull(ContractTerm) or ContractTerm>=NextPayment,
   NextPayment))))))) as ContractEndTmp;

[Gysbert_Wassenaar]

Can you spell out the exact logic for me?