Cumulative Count and Cumulative Distinct Count - Backend Script

[Purushothaman]

Hi @ogster1974 ,

One last help from your end.  Below is my raw table. 

My Requirement is: 

1) Cumulative Count of Account throughout AccountOpendate where Account Status = A

2) Cumulative Distinct Count of Account throughout AccountOpendate where Account Status = A

I would need to achieve this backend without limiting the rows by using where clause.

My end Results in backend should be:

I have the raw file for your reference. 

As I stuck in this logic for few days, your help will be grateful. 

Thank you!

[ogster1974 · Accepted solution]

This works.

Raw:
LOAD
Account,
Party,
AccountOpendate,
"Account Status"
FROM [lib://Community:DataFiles/Req1701.xlsx]
(ooxml, embedded labels, table is Raw);

NoConcatenate
EndResult:
Load
*,
If(Account='A1',1,
If("Account Status"='A',Peek('AccountStatus_A_Accumulated_Total')+1, Peek('AccountStatus_A_Accumulated_Total'))
) as AccountStatus_A_Accumulated_Total,
If(Party='AA',1,
If("Party"<>Peek("Party"),Peek('Party_Accumulated_Total')+1, Peek('Party_Accumulated_Total'))
) as Party_Accumulated_Total
Resident Raw
Order By [Party]
;