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

[kaanerisen]

Hi Uğur,

Try this expressions for your questions:

1. count({<[Seller ID]={"=sum([AR Amt USD])>0.01"}>}distinct [Seller ID])

2. count({<[Seller ID]={"=sum([AR Amt USD])>0.01"},[Suspend Indicator]={"Suspended"}>}distinct [Seller ID])

3. count({<[Seller ID]={"=sum([AR Amt USD])>0.01"},[Aging Bucket]-={"1-30*","31-60*","61-90*"},[Suspend Indicator]={"Not Suspended"}>}distinct [Seller ID])

[ugurarslan]

HI Kaan,

I tried:

count({<[Seller ID]={sum([AR Amt USD])>'0.01'}>}distinct [Seller ID]) it gives me an error... do you know what I am doing wrong?

Thanks

[rangam_s]

Count({<[AR Amt USD]={">$(=0.01)"}>}distinct [Seller ID])

Try this.

[kaanerisen]

count({<[Seller ID]={"=sum([AR Amt USD])>0"}>}distinct [Seller ID])

[Chanty4u]

try this

first see this  result    =sum([AR Amt USD])>0.01

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

or

count({<[Seller ID]={"=sum([AR Amt USD])>0"}>}distinct [Seller ID])

[ugurarslan]

Dear Chanty, this one give me the result I was looking for, very close. Question, if I want to add another criteria, like [Suspend Indicator]={"Suspended"} or I would like to see all AR for [AR Event Date]={"<=$(=Today())"}, what should I type in?

count({<[Seller ID]={"=sum([AR Event Date]={"<=$(=Today())"},[AR Amt USD])>0.01"}>}distinct [Seller ID])??

[Chanty4u]

try this

count({<[Suspend Indicator]={"Suspended"}, [AR Event Date]={"<=$(=Today())"},[Seller ID]={"=sum([AR Amt USD])>0.01"}>}distinct [Seller ID])

[ugurarslan]

Hi Chanty, unfortunately

count({<[Suspend Indicator]={"Suspended"}, [AR Event Date]={"<=$(=Today())"},[Seller ID]={"=sum([AR Amt USD])>0.01"}>}distinct [Seller ID])

didn't work for me.

The result doesn't change, I still get the AR of today and not of yesterday, and it doesn't filter out the suspension data.

[rangam_s]

count({<[Suspend Indicator]={'Suspended'}, [AR Event Date]={"<$(=Today())"},[Seller ID]={"=sum([AR Amt USD])>0.01"}>}distinct [Seller ID])

Above one will give seller IDs as of yesterday.

count({<[Suspend Indicator]={'Suspended'}, [AR Event Date]={"$(=Today()-1)"},[Seller ID]={"=sum([AR Amt USD])>0.01"}>}distinct [Seller ID])

Above one will give you the seller IDs only for yesterday.

Note: If it is text then you need to enclose it in single quote not in double quotes.

[ugurarslan]

I have found to solution and closing this discussion