6 Replies Latest reply on Aug 23, 2018 4:31 AM by Ivan Monnier

# Count Number of clients who change status each period

Hello,

My question may have been addressed but I did not find answers to it.

I have clients who can have one or more contracts, each conctratc makes a line each month.

Each month, a contract has a status (Y or N).

With a LoD, I have calculated the client's status: If one of his contracts is Y, then the client's status is Y.

I want, each month to count the number of clients whose status hase changed, and what was the change (Y to N, N to Y, no change).

I have been able, with a table calculation, to detect the status changes for each client, but the count is false.

I would also prefer not to use table calcs because I have to give the workbook to the users and they do not master table calcs.

I think that LoD could help, but I can't find it.

Here is chat I would like

Detail per client

 Custid Contid janv-18 févr-18 mars-18 avr-18 mai-18 juin-18 juil-18 août-18 sept-18 oct-18 nov-18 déc-18 A N N N N N Y Y Y Y Y Y Y N>Y A 1 N N N N N Y Y Y Y N N N A 2 N N N N N Y Y Y Y Y Y Y A 3 N N N Y N N N B Y Y Y Y N N N N N N N Y Y>N N>Y B 4 Y Y Y Y N N N N N N N Y C N N N N N N Y N>Y C 5 N N N N C 6 N N Y

Count

 janv-18 févr-18 mars-18 avr-18 mai-18 juin-18 juil-18 août-18 sept-18 oct-18 nov-18 déc-18 N>Y 1 2 Y>N 1 No Change 3 3 3 3 2 2 3 3 3 3 3 1

I attach a sample of data, as well as my workbook, though it is not very advanced.

Ivan

• ###### 2. Re: Count Number of clients who change status each period

Hi Zhang,

I had thought of duplicating the datasource and merge it with itself, but I did not want to because the prod datasource is quite large and this would have duplicated the extracts too.

I did not think of linking the source data.

I will check the impact on the final size of the extract, I would not like the size to double.

I have another (small) problem with this. I use a parameter for time granularity (month & quarter).

In the self join clause, I can not use the parameter, it has to be written 'month or 'quarter'.

Thank you.

Ivan

PS: if you think of a solution that can be done without self join, I am interrested

• ###### 3. Re: Count Number of clients who change status each period

Hi Ivan,

An alternative solution depends on how you want to present the data. Do you want to see the status change for all customers and all months at the same time? If yes, then it might be tough.

However, if you want to select one month at a time and see the status change for all customers then I might have an alternative solution for you.

Ossai

• ###### 4. Re: Count Number of clients who change status each period

Hello Ossai,

I am not totally sure what the users want, but I am sure that they want to see the evolution, like this

 janv-18 févr-18 mars-18 avr-18 mai-18 juin-18 juil-18 août-18 sept-18 oct-18 nov-18 déc-18 N>Y 1 2 Y>N 1 No Change 3 3 3 3 2 2 3 3 3 3 3 1

Is it the 'tough' alternative?

Thank you

Ivan

• ###### 5. Re: Count Number of clients who change status each period

Hi Ivan,

This was a very tough one. I thought it was impossible but I finally got it working using an LOD approach. The solution is not pretty but this is the best you can get without a self a join or table calculation. The summary you posted in your previous comment is not correct in the month of May and June. No Change status should be 1 and not 2. Total number of clients is 2 and not 3.

Assumptions:

• Solution is evaluated within a single calendar year. This means client status resets every January.
• Previous year December status will not be looked up, so January will be orphaned and will have a 'No Change' status. This can be tweaked but I'll leave that to you to sort out if you wish. Step 1: Create calculated field [Final Status]

{FIXED [Custid], [Date]: MAX([Statut])}

Step 2: Create calculated field [Status Change]

{FIXED [Custid], YEAR([Date]), [Date]: MAX(

IF DATEPART('month', [Date]) = 1 THEN 'No Change'
ELSEIF DATEPART('month', [Date]) = 2 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 1 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 1 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 1 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 3 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 2 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 2 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 2 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 4 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 3 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 3 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 3 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 5 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 4 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 4 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 4 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 6 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 5 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 5 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 5 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 7 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 6 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 6 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 6 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 8 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 7 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 7 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 7 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 9 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 8 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 8 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 8 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 10 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 9 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 9 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 9 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 11 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 10 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 10 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 10 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END
ELSEIF DATEPART('month', [Date]) = 12 THEN
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 11 THEN [Final Status] END)} =
{FIXED [Custid], [Date]: MAX([Final Status])} THEN 'No Change'
ELSE
IF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 11 THEN [Final Status] END)}  = 'N' THEN 'N -> Y'
ELSEIF {FIXED [Custid], YEAR([Date]): MIN(IF DATEPART('month', [Date]) = 11 THEN [Final Status] END)} = 'Y' THEN 'Y -> N'
ELSE 'No Change'
END
END

END
)} Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 6. Re: Count Number of clients who change status each period

Hello Ossai,

1- You are right, and I saw my mistake after posting.

2- According to the length of the formula, I agree it has been a tough one

I will have a look at it and see if I can manage the year change.

I yes, I will post my work.

Thank you a lot

Ivan