Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculating customer loyalty

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

!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";

View solution in original post

2 Replies
larouge69
Contributor III
Contributor III

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.

Anonymous
Not applicable
Author

!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";