Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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