19 Replies Latest reply: Jun 29, 2018 4:45 AM by Ugur Arslan

# 3 Count questions for financial purposes

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

• ###### Re: 3 Count questions for financial purposes

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

• ###### Re: 3 Count questions for financial purposes

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.

• ###### Re: 3 Count questions for financial purposes

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

Regards,

Kaushik Solanki

• ###### Re: 3 Count questions for financial purposes

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.

• ###### Re: 3 Count questions for financial purposes

Dear Kaushik,

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

Ugur

• ###### Re: 3 Count questions for financial purposes

Then try this.

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

Regards,

Kaushik Solanki

• ###### Re: 3 Count questions for financial purposes

Hi Kaushik,

This still gives me not the correct result:

so if write:

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

• ###### Re: 3 Count questions for financial purposes

Regards,

Kaushik Solanko

• ###### Re: 3 Count questions for financial purposes

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

• ###### Re: 3 Count questions for financial purposes

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])

• ###### Re: 3 Count questions for financial purposes

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

• ###### Re: 3 Count questions for financial purposes

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

Try this.

• ###### Re: 3 Count questions for financial purposes

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

• ###### Re: 3 Count questions for financial purposes

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])

• ###### Re: 3 Count questions for financial purposes

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])??

• ###### Re: 3 Count questions for financial purposes

try this

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

• ###### Re: 3 Count questions for financial purposes

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.

• ###### Re: 3 Count questions for financial purposes

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.

• ###### Re: 3 Count questions for financial purposes

I have found to solution and closing this discussion