Sum total AR

[ugurarslan]

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

[ogautier62]

Hi,

I think you could do like this :

sum({<SellerId = P(' your expression for the count')>} sales)

regards

[Digvijay_Singh]

This works -

Sum({<[Seller ID]={"=Sum({<[Suspend Indicator]={'Suspended'},[AR Aging Bucket]={'91-120 days','121-150 days','151-180 days','181+ days'}>}[AR Amt USD])>0"}>}[AR Amt USD])