Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
!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";
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))
IDACCOUNT | PAYMENT | MONTH | YEAR | VALUE | =Count({<VALUE= >} VALUE)/Num(Mode(MONTH)) |
191047515 | YES | 01 | 2016 | 1 | 100,00% |
191047515 | NO | 02 | 2016 | 0 | 50,00% |
191047515 | YES | 03 | 2016 | 1 | 33,33% |
I guess it should be aggregated by IDACCOUNT.
!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";