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

    Top X or make selection

    Mikey Michaels

      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!




        • 1. Re: Top X or make selection
          Shinichiro Murakami

          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.


          [SM_Header Selected calendar]

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

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



          [SM_Header Selected category]

          if [Select View] = "Business Segment" then [Business Segment]

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

          elseif [Select View] = "Product Business Line" then [Product Business Line]




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



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


          Add parameter


          Set filter


          SEt sort.




          • 2. Re: Top X or make selection
            Mikey Michaels

            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




            • 3. Re: Top X or make selection
              Shinichiro Murakami

              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.


              [SM_Header Selected category Filter]

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






              • 4. Re: Top X or make selection
                Mikey Michaels

                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:





                • 5. Re: Top X or make selection
                  Shinichiro Murakami

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



                  • 6. Re: Top X or make selection
                    Mikey Michaels

                    Hi Shin,

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

                    • 7. Re: Top X or make selection
                      Shinichiro Murakami

                      No problem,

                      You are welcome at any time.