1 2 Previous Next 16 Replies Latest reply on Jun 17, 2016 5:12 PM by Rupali Negi

# Rank the dimension and pick largest

Hi Friends,

I am running into a problem. It is kind of complicated and I am not sure how to solve it.

So my data is somehow like this..

I have bunch of customer ID with their market code grouping into one organisation ID(OEID).

For example 3015243005. This OE ID has 8 Customer ID grouped into it. Each customer ID has its own market code.

So my requirement is I have to see maximum number of distinct market code inside any OE ID and give the OE ID with that market code.Means here OE ID 3015243005 has bunch of Customer id, and the market code occurring most inside this OE ID is TECH_SER, so we will give this OE ID a single market code as TECH_SER.

I am thinking this can be achieved by total count of the distinct market code occurring inside an OE ID and then picking the greater one.Friends can you help me figure out how to solve this??I am novice and it seems a little complicated problem for me.

• ###### 1. Re: Rank the dimension and pick largest

fixed [organisationid ],[customerid ] : max(countd(marketcode)

1 of 2 people found this helpful
• ###### 2. Re: Rank the dimension and pick largest

Hi Please find the formula below.

{fixed [OrgID],[Customerid] : max(CountD(Market_Code))}

Same thing can also be achieved using Windows_Functions. I will post the code for the same soon.

Regards,

Ashish Chaudhari

2 of 3 people found this helpful
• ###### 3. Re: Rank the dimension and pick largest

Hi Rupali,

You can also use this formula.

WINDOW_MAX(COUNTD([Product Name]))

Refer to the similar problem below. i want max count of product for a category.

See the result.

Change the compute using like this. In your case this would be Org_ID

Thanks and Regards,

Ashish Chaudhari

2 of 2 people found this helpful
• ###### 4. Re: Rank the dimension and pick largest

Please find the attached workbook for the reference.

• ###### 5. Re: Rank the dimension and pick largest

Hi,

Is that what you wanted?

I've created a dummy data like yours.

So according to your requirement result should be

If so then follow the attached workbook (version 9.2).

Let me know if you've any query.

Mahfooj

• ###### 6. Re: Rank the dimension and pick largest

Hi Ashish,

Thanks for the reply. I cant see the attached workbook.Can you please forward it again.

• ###### 7. Re: Rank the dimension and pick largest

Hi Mahfooz,

Thanks for the reply. Can you please forward me the workbook.I cant find it attached.

• ###### 8. Re: Rank the dimension and pick largest

Its already attached. Try to open the original post in a new tab

See the image I've attached the workbook.

1 of 1 people found this helpful
• ###### 9. Re: Rank the dimension and pick largest

Hi Rupali,

Did you get the attachment?

Mahfooj

• ###### 12. Re: Rank the dimension and pick largest

Hi Mahfooj Khan,

i did yes. Your answer served the purpose. However there is slight change in requirement. I tried to do it but was unable to get it. I further need to paste my problem statement.Will you be able to look into it?

Thanks

Roopali

• ###### 13. Re: Rank the dimension and pick largest

Kindly mark my answer as correct so that this thread gets complete.

Generally you should create a new thread for new question.

• ###### 14. Re: Rank the dimension and pick largest

Hi Mahfooj Khan,

Yes sure. I will take care of it going on.

Actually problem which I am having is :

My occurance formula is: {FIXED [Oe Id],[Vertical Market Top Code]:COUNTD([Cr Party Id])}

Rank is just as same desc of sum(occurance)

Now problem is I can see many OE ID's which have distinct Vertical code >1. This means this OE ID has data like

 OE ID VERTICAL_CODE cr_party_id 3000013115 PROF_SRV 125963475 3000013115 ENT 7902770

I want to filter all such OE ID's which has more than one Vertical _code. For this I created a filter as "OE VD TWO VMS" (IF {FIXED [Oe Id] : COUNTD([Vertical Market Top Code])} > 1 THEN 'Show' ELSE 'Hide' END)

as shown below.But somehow not getting correct result.As we can see below the OE ID with single occurrence is still showing up.Ex:3000181917.

Can you help me here.

1 2 Previous Next