2 Replies Latest reply: Jul 18, 2018 5:47 AM by Ravichandra N RSS

    Calculating customer loyalty

    Jose Gonzalez

      Hi, I hope someone can help me with this

      I have a table that describes the payments (1 if there was payment & 0 if there was no payment) of an account in a period.


      IDACCOUNT       PAYMENT       MONTH        YEAR


      191047515              YES           JANUARY      2016

                            

      191047515                NO          FEBRUARY     2016


      191047515                YES            MARCH       2016


       

      Up there all good. But I need a column that will define the fidelity of that account, that is:


      In the first record as it is the first payment would have a 100% fidelity

      In the second there was no payment, but there are already two payment records so it would be 1/2 or better said 50%

      In the third register if there was payment, then there are 3 payment records but only 2 were made. Then we would have 2/3 or rather 66%


      IDACCOUNT       PAYMENT       MONTH        YEAR         %FIDELITY


      191047515              YES           JANUARY      2016                   1/1

                            

      191047515                NO          FEBRUARY     2016                    1/2

       

      191047515                YES            MARCH       2016                     2/3

       

      How do I calculate that field, in SCRIPT?

        • Re: Calculating customer loyalty
          David B.

          Hi Jose,

           

          I quickly made this as Expression : (I assume that VALUE field contain 1 if payment = yes and 0 if it contains no, and i turned the month on a month number)

          =Count({<VALUE= >} VALUE)/Num(Mode(MONTH))

           

           

          IDACCOUNTPAYMENTMONTHYEARVALUE=Count({<VALUE= >} VALUE)/Num(Mode(MONTH))
          191047515YES0120161100,00%
          191047515NO022016050,00%
          191047515YES032016133,33%

           

          I guess it should be aggregated by IDACCOUNT.

          • Re: Calculating customer loyalty
            Ravichandra N

            !Hi!

            Use COUNT/NUMBER as expression on the frontend. You need to aggregate the data by accountID in your case, I assume. This script below might not be ideal, but sure will give you an idea.

             

            TEMP:

            LOAD

                IDACCOUNT,

                PAYMENT,

                DATE#(MONTH,'MMM') AS MONTH,

                "YEAR"

            FROM [lib://Community/308201.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

             

            NoConcatenate

             

             

            TEMP1:

            LOAD *, RowNo() AS COUNT

            Resident TEMP

            WHERE PAYMENT='YES'

            ORDER BY MONTH;

             

             

            NoConcatenate

             

             

            TEMP2:

            LOAD *, RowNo() AS COUNT

            Resident TEMP

            WHERE PAYMENT='NO'

            ORDER BY MONTH;

             

             

            drop table TEMP;

             

             

            Concatenate(TEMP1)

            LOAD *,1

            Resident TEMP2;

             

             

            DROP TABLE TEMP2;

             

             

            FINAL:

            LOAD *,RowNo() AS NUMBER

            Resident TEMP1

            order by MONTH;

             

             

            DROP TABLE TEMP1;

            DROP FIELD "1";