Cumulative Count on Pivot table that Ignores other Date

Badzreyes00 — Sat, 16 Nov 2024 17:58:33 GMT

Hi Everyone,

I have a requirement that I really hope you can help me with since I find it very complex for me.

In my current data model, I am using a Date Bridge and one of date reference is "Open_Account_Date" or "OPENACCT" so I am using this date reference for my Pivot Table.

Below is my formula for Cumulative Open Account Count

RangeSum(Before(count({$<DateBasis={"OPENACCT"},Account_Status={'OPEN'}, DateNum={"<=$(=Max(DateNum))"}, Year=, Month=, Week=, Day=, Date= >}distinct Account_No), 0, ColumnNo()))

In my current output I am already not counting those with CLOSE account status even though they are still Open on those Month. Kindly see below sample data.

Sample Account Table:

Person_No	Account_No	Account_Type	Open_Account_Date	Account_Status	Close_Date
10001	21111	ACT02	Jan-2020	OPEN	NULL
10002	21112	ACT01	Jan-2020	CLOSE	May-2020
10002	21113	ACT02	Jan-2020	OPEN	NULL
10003	23824	ACT01	Jan-2020	CLOSE	May-2020
10004	23876	ACT01	Feb-2020	OPEN	NULL
10004	33218	ACT02	March-2020	OPEN	NULL
10004	98812	ACT03	April-2020	CLOSE	June-2020
10005	98816	ACT03	Feb-2020	OPEN	NULL
10005	27584	ACT02	April-2020	OPEN	NULL
10006	57482	ACT01	March-2020	OPEN	NULL
10007	19284	ACT02	April-2020	OPEN	NULL
10008	17885	ACT01	March-2020	OPEN	NULL
10009	19960	ACT02	Feb-2020	CLOSE	March-2020
10009	17741	ACT03	March-2020	OPEN	NULL
10009	19573	ACT01	April-2020	OPEN	NULL
10010	94717	ACT02	April-2020	CLOSE	May-2020
10010	43814	ACT03	Feb-2020	OPEN	NULL
10011	48319	ACT01	April-2020	CLOSE	June-2020
10011	76598	ACT03	May-2020	OPEN	NULL
10012	77526	ACT02	Jan-2020	CLOSE	Feb-2020
10012	55632	ACT03	May-2020	OPEN	NULL

Current Output - Cumulative Open Account Count

Open Count
Account Type	Jan-20	Feb-20	Mar-20	Apr-20	May-20
ACT01	0	1	3	4	4
ACT02	2	2	3	5	5
ACT03	0	2	3	3	5

Expected Output - Cumulative Open Account Count

Open Count
Account Type	Jan-20	Feb-20	Mar-20	Apr-20	May-20
ACT01	2	3	5	7	5
ACT02	3	3	3	6	6
ACT03	0	2	3	4	6

I will really appreciate any input please. Thank you so much in advance!

Re: Cumulative Count on Pivot table that Ignores other Date

sunny_talwar — Tue, 13 Oct 2020 17:59:29 GMT

@Badzreyes00 try something like this instead

Table: LOAD Person_No, Account_No, Account_Type, Date#(Open_Account_Date, 'MMM-YY') as Open_Account_Date, Account_Status, Alt(Date#(Close_Date, 'MMM-YY'), MonthStart(Today())) as Close_Date; LOAD * INLINE [ Person_No, Account_No, Account_Type, Open_Account_Date, Account_Status, Close_Date 10001, 21111, ACT02, Jan-20, OPEN, NULL 10002, 21112, ACT01, Jan-20, CLOSE, May-20 10002, 21113, ACT02, Jan-20, OPEN, NULL 10003, 23824, ACT01, Jan-20, CLOSE, May-20 10004, 23876, ACT01, Feb-20, OPEN, NULL 10004, 33218, ACT02, Mar-20, OPEN, NULL 10004, 98812, ACT03, Apr-20, CLOSE, Jun-20 10005, 98816, ACT03, Feb-20, OPEN, NULL 10005, 27584, ACT02, Apr-20, OPEN, NULL 10006, 57482, ACT01, Mar-20, OPEN, NULL 10007, 19284, ACT02, Apr-20, OPEN, NULL 10008, 17885, ACT01, Mar-20, OPEN, NULL 10009, 19960, ACT02, Feb-20, CLOSE, Mar-20 10009, 17741, ACT03, Mar-20, OPEN, NULL 10009, 19573, ACT01, Apr-20, OPEN, NULL 10010, 94717, ACT02, Apr-20, CLOSE, May-20 10010, 43814, ACT03, Feb-20, OPEN, NULL 10011, 48319, ACT01, Apr-20, CLOSE, Jun-20 10011, 76598, ACT03, May-20, OPEN, NULL 10012, 77526, ACT02, Jan-20, CLOSE, Feb-20 10012, 55632, ACT03, May-20, OPEN, NULL ]; Dates: LOAD Account_No, Date(MonthStart(Open_Account_Date, IterNo()-1), 'MMM-YY') as Date Resident Table While MonthStart(Open_Account_Date, IterNo()-1) <= Close_Date;

One you do this, all you would need is two dimensions (Account_Type and Date) and Count(DISTINCT Account_No) as your expression

topic Re: Cumulative Count on Pivot table that Ignores other Date in App Development

Cumulative Count on Pivot table that Ignores other Date

Re: Cumulative Count on Pivot table that Ignores other Date