3 Count questions for financial purposes

[ugurarslan]

Dear all,

I need the answer for the following three questions:

First Question:

How many Seller ID's do have AR?

Answer: 12

Expression:

Second Question

How many Seller ID's  with an AR suspended?

Answer: 7

Expression:

Third Question

How many Seller ID's with an AR 90+ are not suspended?

Answer = 3 account

I have the following dimensions:

Aging buckets

Current

1-30 days

31-60 days

61-90 days

91-120 days

121-150 days

150-181 days

181+ days

Suspend Indicator

Suspended

Not Suspended

Seller ID
1
4
7
90
660
3456
5677
89877
321234
445444
456778
7883983
8498989
44929029

and the following measurement:

AR amt USD

with all different AR amounts

I created the following pivot

Pivot Table
Collumn	=[Seller ID]
	=[Suspend Indicator]
Row	=[Aging Buckets]
Measurement	=sum([AR amt USD])

Result of the pivot=

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

Thanks for helping me out... I just couldn't manage it.

Ugur

[kaushiknsolanki]

Hi,

Try this.

For 1: Count(Distinct [Seller ID])

For 2: Count({<SuspendIndicator = {"Suspended"}>}Distinct  [Seller ID])

For 3: Count({<SuspendIndicator = {"Non Suspended"},Bucket = {"90*","121*","150*","181*"}>}Distinct  [Seller ID])

Regards,

Kaushik Solanki

[ugurarslan]

Hi Kaushik,

When I do For 1: Count(Distinct [Seller ID]) it also gives me the result for all seller id's with or with out an AR (result 14). I need to have the count for all accounts with an AR only, so the correct result should be 12 seller ID.

[kaushiknsolanki]

How do one know if the ID is AR or not?

Regards,

Kaushik Solanki

[ugurarslan]

Hi Kaushik, if the seller ID has an AR ($ balance) more than 0.01$ the seller ID has and AR. If the seller ID has not balance (so no amount in one of the aging buckets) is has no AR.

[ugurarslan]

Dear Kaushik,

If AR = greater than 0.01$ than please give me the distinct count of the seller ID.

Ugur

[kaushiknsolanki]

Then try this.

Count({<AR = {">0.01"}>}Distinct SellerID)

Regards,

Kaushik Solanki

[ugurarslan]

Hi Kaushik,

This still gives me not the correct result:

so if write:

count({<[AR Amt USD]={'>0.01'}>}distinct [Seller ID]) = 14

[kaushiknsolanki]

Share your data.

Regards,

Kaushik Solanko

[ugurarslan]

How can I share my data? It is in enterprise.