1 Reply Latest reply on Nov 14, 2016 1:20 PM by Jordan Ross

    Change title based on number of alerts (when rate passes specified threshold)

    Jordan Ross

      Hi All,

       

      So I am working on final touches of a dashboard/workbook, and one of the views has a map that will display a dot any time a rate goes above a certain threshold (>5%), and a table next to it that lists the top 20 locations and associated rates that are above thereshold or all locations that are above the threshold if there are less than 20.  There are also 10 regions that the user can select using a global filter (typically only 1 region at a time).

       

      I want to make a title that states "Top 20 Locations ranked by maximum rate" if there are 20 (or more) alerts, or "All locations ranked by maximum rate" if there are less than 20 alerts.

       

      How would I go about doing this?

       

       

       

      I tried making made a calculated field which could be put into a simple workbook which is then placed in the view to act as a title. However, I can't figure out how to make the calculated field count what I want it to. I tried:

       

      IF (MAX([rate]) >5) and (COUNTD([Location Name]) >=20) THEN "Top 20 locations" ELSE "All Locations"

       

      and combined it with the global region filter. However, it seems to ignore the filter and identify that there are greater than 20 locations and there is a rate above 5%, so it always displays "Top 20 locations".

       

      I tried changing to to just say:

       

      IF (COUNTD([Location Name]) >=20) THEN "Top 20 locations" ELSE "All Locations"

       

      and combined it with the same filters used to limit the chart to the top 20 alerts but that still had no effect. However, if I put it into the chart title it will instead say "All Locations" even if there are 20 or more alerts.

       

       

      Unfortunately I can't share the workbook because of confidentiality issues.

        • 1. Re: Change title based on number of alerts (when rate passes specified threshold)
          Jordan Ross

          In case anyone comes across this in the future, I ended up coming up with a working solution.

           

          It required a series of nested calculations, some of which were already made for the rate calculations.

           

           

           

          Header 1Header 2
          RATE CALCULATION{include [Date]:sum([REGION INDICATOR])}/{fixed [LOCATION NAME]: avg([LOCATION POPULATION])}*100
          MAXIMUM OBSERVED RATE{fixed [Date],[LOCATION NAME]:MAX([RATE CALCULATION])}
          NUMBER OF LOCATIONS ABOVE RATE OF 5

          if {include [LOCATION NAME]:MAX([MAXIMUM OBSERVED RATE)}>5 then 1

          else 0

          END

           

          (note: the 5 is the rate above which we want alerts for)

          TITLE CALCULATION

          IF SUM([NUMBER OF LOCATIONS ABOVE RATE OF 5])>=20 THEN "Top 20 locations" ELSE "All locations"

          END

           

          The period for which it calculated the rates to within the last week by using a filter in the worksheet.

           

          [NUMBER OF LOCATIONS ABOVE RATE OF 5] was added to the worksheet under "detail" using the SUM measure.

           

          Finally, [TITLE CALCULATION] was added to detail, with the rest of the title written out.