2 Replies Latest reply on Mar 12, 2016 2:09 PM by Paul Albert

    Trying to compare ratings at point of time and get aggregate count

    Paul Albert



      I've been spinning my wheels with this challenge:


      1. Take ratings (which are updated irregularly) by investment title and, for any date, find the most current rating. (I believe I have achieved that).

      2. Then, for each Bureau the investment title belongs to, count up the number of Green/Yellow/Red ratings.  (I've run into a wall here).


      Ultimately, I want to show the percentage for each Bureau of Green/Red/Yellow investments.


      Attached is the problem set I am working with, any help would be much appreciated!

        • 1. Re: Trying to compare ratings at point of time and get aggregate count
          Mary Solbrig

          For question 1:
          What you did looks fine, although it goes a bit smoother if you convert Evaluation into a Number and move it into Measures instead of Dimensions.


          For question 2:

          Are you looking for the raw number of Green/Red/Yellow investments, or days in which the most recent rating was Green/Yellow/Red? As in, would you like the computation done on the filled in values, or the raw values?


          For the raw values, since that is easier: Place Bureau and Color on the Rows shelf, and Number of Records on the Columns shelf. For percent of Total, use LOD similar to follows:

          SUM([Number of Records])/

          ATTR({EXCLUDE [Rating Color]: SUM([Number of Records])})


          For the filled in values, to determine the percent of Months where the most recent color was red/yellow/green:


          To see a running percentage, create 3 calculated fields, Running Red, Running Green, and Running Yellow as follows:


          IF [Running Rating Color] = "Yellow" THEN 1 ELSE 0 END



          For a percentage including the null days at the beginning, use [Running Red]/INDEX() etc. To exclude the days at the beginning before there are records, create a calc "Has Record":


          IF ISNULL([Running Rating Color]) THEN 0 ELSE 1 END



          And use [Running Red]/[Has Record]


          See Running Percent Exclusive and Running Percent Inclusive tabs of attached workbook.


          For just the final totals, no cool filled running chart, add LAST() = 0 to the Filters shelf.

          • 2. Re: Trying to compare ratings at point of time and get aggregate count
            Paul Albert

            Hi Mary,


            Thanks so much for this start!


            I should have been clearer, I want to apply a filter that would allow me to show by Bureau the distribution of investment ratings.  Investment ratings are irregularly updated, and for any given month, if they have not been changed inherit the most the recent rating.  I want to show the distribution of ratings for a particular month and compare them with the distribution of ratings for the that month one year previous.


            I've reattached a workbook with a picture showing what my ultimate goal is.


            I've been noodling with your suggestions and can not take them to the next step I am hoping to taking them to.


            I'd appreciate any suggestions on how to get to this end goal is.


            Thanks so much!