7 Replies Latest reply on Oct 12, 2016 11:12 AM by Shinichiro Murakami

# Top X or make selection

Hello All,

I have a question I hope someone can help me answer. I'm trying to setup a Top X calculated field that would be based off the "Select View" and "Select Date Level" parameter.

Here are the conditions I need to implement:

1. Top X Product Brand based off the last month or quarter (which ever is selected from the "Select Date Level" parameter
1. For instance, if "month' was selected on the "Select Date Level" parameter, I would expect Teamcenter to rank #1 as they had far and away more Seats in 2016009 than any other
2. Have the option of selecting Top X brands OR manually select a Product Brand(s)

All help is much appreciated!

Cheers,

Mikey

• ###### 1. Re: Top X or make selection

HI, Mikey

Seems like your data is just a sample and have a lot of null.

Then I'm not sure I understood your request correctly, but anyways.

if [Select Date Level] = "Month" then [SM_Month]

elseif [Select Date Level] = "Quarter" then [SM_Quarter]

end

ELSEif [Select View] = "Product Brand" then [Product Brand]

end

[SM_Month]

"M"+str(year([Calendar Day])*100+month([Calendar Day]))

[SM_Quarter]

"Q"+str(year([Calendar Day])*100+datepart('quarter',[Calendar Day]))

[SM_Rank Criteria]

sum(if [SM_Header Selected calendar]={fixed:max([SM_Header Selected calendar])} then [Seat Quantity Under Maint] end)

Set filter

SEt sort.

Thanks,

Shin

• ###### 2. Re: Top X or make selection

Thank you so much Shinichiro for you prompt reply-> much appreciated!

I think I may have been confusing when posting my question. I will try and clarify.

What I'm after is a Top N filter, based off the product brand. To calculate the Top N, I need the calculated field to look at the last month in the data set (in this case it's 2016009).  Below is an example if the Top N was set to 3 Product Brands:

These 3 Product Brands meet the criteria from above and are sorted in descending order based off of 2016009.Also, I do want the other months to remain in the view as shown above.

Finally, I would like the user to be able to choose whether they would like to implement the TOP N filter OR they can manually select a product brand or brands.

I hope this makes more sense. Sorry for being confusing

Regards,

Mikey

• ###### 3. Re: Top X or make selection

Just quick modification one.  Of course there may be much better solutions.

Change the parameter like below.

Pick some big number as Value and display as "user select" or something.

Again, your sample data is not sufficient enough and there is only one time period.

if [TOPN]=100 then [SM_Header Selected category] else "User Filter is not Selected" end

Thanks,

Shin

• ###### 4. Re: Top X or make selection

Hi Shin,

Thank you so much for your help- much appreciated!

You are very very close to what I need. Instead of only presenting only the last month or quarter in the bar chart, how could I adjust your viz so that all months or quarters are show and the sorting is based of the last month or quarter?

I'm looking for the presentation to look like the following:

Cheers,

Mikey

• ###### 5. Re: Top X or make selection

I don't understand which date column you are using could you attach whole screenshot.

Shin

• ###### 6. Re: Top X or make selection

Hi Shin,

I was able to figure it out. Thank you so much for your time!

• ###### 7. Re: Top X or make selection

No problem,

You are welcome at any time.

Shin