Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
How do one know if the ID is AR or not?
Regards,
Kaushik Solanki
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.
Dear Kaushik,
If AR = greater than 0.01$ than please give me the distinct count of the seller ID.
Ugur
Then try this.
Count({<AR = {">0.01"}>}Distinct SellerID)
Regards,
Kaushik Solanki
Hi Kaushik,
This still gives me not the correct result:
so if write:
count({<[AR Amt USD]={'>0.01'}>}distinct [Seller ID]) = 14
Share your data.
Regards,
Kaushik Solanko
How can I share my data? It is in enterprise.