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

    Count Number of clients who change status each period

    Ivan Monnier

      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.

       

      Thanks in advance.

       

      Ivan

        • 1. Re: Count Number of clients who change status each period
          Zhouyi Zhang

          Hi, Ivan

           

          Please find my solution by using left join your data to itself as shown below

           

           

          Step 2, create 3 calculation fields as below

          And then build the view using calculation 2

           

           

          Hope this helps

           

          ZZ

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

            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
              Okechukwu Ossai

              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
                Ivan Monnier

                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
                  Okechukwu Ossai

                  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
                    Ivan Monnier

                    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