4 Replies Latest reply on Apr 18, 2019 3:03 AM by Ricky Walsh

    Calculation apply dynamic value to filter results

    Joshua Gaffney

      Apologies if this has been asked before but I've searched around for a few hours and made little progress.

       

      I've got a simple calculation to filter entries into 3 categories from my data source. [Amount] is a value in USD but formatted as a number, [Age] is how long the entry has existed for in days, again formatted as a number.

       

      IF [Amount] >= 100 AND [Age] >= 60 THEN 'Category 1'

      ELSEIF [Amount] >= 100 AND [Age] > 30 AND [Age] <= 60 THEN 'Category 2'

      ELSE 'Category 3'

      END

       

      I'm attempting to apply a variable benchmark score for each category. So if 5% of entries are "category 1", the benchmark score for "category 1" will be '2'. If 10% of entries are "category 2" the benchmark score for "category 2" will be '1'. I'm planning to then multiply this "variable benchmark score" for each category against a fixed risk value that I've set for each category.

       

      The end result will be a dynamic visual representation of how much risk there is in each category.

       

      I'm trying to do this by creating a calculated field, however I'm struggling to create a calculation that can identify the % of entries in each category.