Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ugurarslan
New Contributor

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 IDSuspend IndicatorCurrent1-30 days31-60 days61-90 days91-120 days121-150 days150-181 days181+ days
1Suspended$5.00$67.00$67.00$-  $7.00$74.00$734.00$- 
4Not Suspended$66.00$7.00$-  $7.00$8.00$-  $-  $73.00
7Suspended$7.00$78.00$8.00$-  $-  $-  $-  $- 
90Not Suspended$-  $-  $-  $-  $-  $-  $-  $- 
660Not Suspended$7.00$-  $6.00$3.00$3.00$67.00$7.00$- 
3456Suspended$-  $8.00$-  $-  $-  $4.00$3.00$7.00
5677Not Suspended$7.00$8.00$7.00$-  $-  $-  $-  $- 
89877Suspended$8.00$-  $-  $7.00$7.00$-  $-  $3.00
321234Not Suspended$-  $-  $-  $-  $-  $-  $-  $- 
445444Suspended$888.00$-  $-  $8.00$8.00$-  $3.00$- 
456778Not Suspended$-  $-  $5.00$-  $-  $7.00$7.00$- 
7883983Suspended$87.00$-  $-  $45.00$8.00$-  $3.00$7.00
8498989Not Suspended$-  $85.00$-  $-  $-  $-  $-  $- 
44929029Suspended$54.00$-  $5.00$-  $4.00$-  $-  $7.00

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

Ugur

19 Replies

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

ugurarslan
New Contributor

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

ugurarslan
New Contributor

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.

ugurarslan
New Contributor

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

ugurarslan
New Contributor

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

Share your data.

Regards,

Kaushik Solanko

ugurarslan
New Contributor

Re: 3 Count questions for financial purposes

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

Community Browser