Dear all,
I have a table with the following aging
| Seller ID | Suspend Indicator | Current | 1-30 days | 31-60 days | 61-90 days | 91-120 days | 121-150 days | 150-181 days | 181+ days |
| 1 | Suspended | $5.00 | $67.00 | $67.00 | $- | $7.00 | $74.00 | $734.00 | $- |
| 4 | Not Suspended | $66.00 | $7.00 | $- | $7.00 | $8.00 | $- | $- | $73.00 |
| 7 | Suspended | $7.00 | $78.00 | $8.00 | $- | $- | $- | $- | $- |
| 90 | Not Suspended | $- | $- | $- | $- | $- | $- | $- | $- |
| 660 | Not Suspended | $7.00 | $- | $6.00 | $3.00 | $3.00 | $67.00 | $7.00 | $- |
| 3456 | Suspended | $- | $8.00 | $- | $- | $- | $4.00 | $3.00 | $7.00 |
| 5677 | Not Suspended | $7.00 | $8.00 | $7.00 | $- | $- | $- | $- | $- |
| 89877 | Suspended | $8.00 | $- | $- | $7.00 | $7.00 | $- | $- | $3.00 |
| 321234 | Not Suspended | $- | $- | $- | $- | $- | $- | $- | $- |
| 445444 | Suspended | $888.00 | $- | $- | $8.00 | $8.00 | $- | $3.00 | $- |
| 456778 | Not Suspended | $- | $- | $5.00 | $- | $- | $7.00 | $7.00 | $- |
| 7883983 | Suspended | $87.00 | $- | $- | $45.00 | $8.00 | $- | $3.00 | $7.00 |
| 8498989 | Not Suspended | $- | $85.00 | $- | $- | $- | $- | $- | $- |
| 44929029 | Suspended | $54.00 | $- | $5.00 | $- | $4.00 | $- | $- | $7.00 |
In case I want to see the count of seller ID's who have an balance above 90 days and are suspended I use the following expression:
| count({<[[Suspend Indicator]={'Suspended'},[AR Aging Bucket]={'91 - 120 days','121 - 150 days','151 - 180 days','181+ days'},[Seller ID]={"=sum([AR Amt USD])>0"}>}distinct [Seller ID]) |
Result is 6 seller ID's who are suspended and have a balance + 90 days.
| Seller ID | Suspend Indicator | Current | 1-30 days | 31-60 days | 61-90 days | 91-120 days | 121-150 days | 150-181 days | 181+ days |
| 1 | Suspended | $5.00 | $67.00 | $67.00 | $- | $7.00 | $74.00 | $734.00 | $- |
| 3456 | Suspended | $- | $8.00 | $- | $- | $- | $4.00 | $3.00 | $7.00 |
| 89877 | Suspended | $8.00 | $- | $- | $7.00 | $7.00 | $- | $- | $3.00 |
| 445444 | Suspended | $888.00 | $- | $- | $8.00 | $8.00 | $- | $3.00 | $- |
| 7883983 | Suspended | $87.00 | $- | $- | $45.00 | $8.00 | $- | $3.00 | $7.00 |
| 44929029 | Suspended | $54.00 | $- | $5.00 | $- | $4.00 | $- | $- | $7.00 |
I have the following questions.
Now I want to get the total balance for only these customers. Result Should be $ 2.128,- (please see orange marked in below table).
| Seller ID | Suspend Indicator | Current | 1-30 days | 31-60 days | 61-90 days | 91-120 days | 121-150 days | 150-181 days | 181+ days |
| 1 | Suspended | $5.00 | $67.00 | $67.00 | $- | $7.00 | $74.00 | $734.00 | $- |
| 4 | Not Suspended | $66.00 | $7.00 | $- | $7.00 | $8.00 | $- | $- | $73.00 |
| 7 | Suspended | $7.00 | $78.00 | $8.00 | $- | $- | $- | $- | $- |
| 90 | Not Suspended | $- | $- | $- | $- | $- | $- | $- | $- |
| 660 | Not Suspended | $7.00 | $- | $6.00 | $3.00 | $3.00 | $67.00 | $7.00 | $- |
| 3456 | Suspended | $- | $8.00 | $- | $- | $- | $4.00 | $3.00 | $7.00 |
| 5677 | Not Suspended | $7.00 | $8.00 | $7.00 | $- | $- | $- | $- | $- |
| 89877 | Suspended | $8.00 | $- | $- | $7.00 | $7.00 | $- | $- | $3.00 |
| 321234 | Not Suspended | $- | $- | $- | $- | $- | $- | $- | $- |
| 445444 | Suspended | $888.00 | $- | $- | $8.00 | $8.00 | $- | $3.00 | $- |
| 456778 | Not Suspended | $- | $- | $5.00 | $- | $- | $7.00 | $7.00 | $- |
| 7883983 | Suspended | $87.00 | $- | $- | $45.00 | $8.00 | $- | $3.00 | $7.00 |
| 8498989 | Not Suspended | $- | $85.00 | $- | $- | $- | $- | $- | $- |
| 44929029 | Suspended | $54.00 | $- | $5.00 | $- | $4.00 | $- | $- | $7.00 |
Can you please help me?
Thanks
Ugur
