Cumulative Count Grouping and Cumulative Distinct Count Grouping - Backend Script

[Purushothaman]

Hi @ogster1974 ,

On my earlier post I missed out the Accumulation by AccountType.  Below is the updated raw table. 

My Requirement is: 

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

2) Cumulative Distinct Count of Party throughout AccountOpendate by Account type 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. 

I am sorry for the inconvenience.  

Many thanks for your help!!!

[ogster1974 · Accepted solution]

try this

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

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

Drop table [Raw];