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

Calculation apply dynamic value to filter results

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.

• 1. Re: Calculation apply dynamic value to filter results

Joshua

sounds like you have been working the problem - please post your twbx workbook to see your approach and where you ran into problems

thanks

Jim

• 2. Re: Calculation apply dynamic value to filter results

Please see attached, I've not anything in it that has really started on this calculation. As the stuff I've tried so far is gunk that has led to a dead end.

If you want some examples of the calculations I've tried, let me know.

• 3. Re: Calculation apply dynamic value to filter results

I have found a solution, however it's not very elegant as I will require a calculated field for each category. Does anyone have a more elegant solution?

IF COUNT([Category] = 'Category 1')/COUNT([Category]) > 0.05 THEN SUM( IF [Category] = 'Category 1' then [Amount] * 2 end )
Else SUM( IF [Category] = 'Category 1' then [Amount] end )
END

• 4. Re: Calculation apply dynamic value to filter results

Hi Joshua,

If Category is always going to be present in the view you could try something like the below:

Category Proportion

SUM([Number of Records])/TOTAL(SUM([Number of Records]))

Benchmark Score

IF ATTR([Category]) = "Category 1" THEN

IIF([Category Proportion] > 0.05, 2, 1)

ELSEIF ATTR([Category]) = "Category 2" THEN

IIF([Category Proportion] > 0.1, 1, 0.5)

// Other Categories

END

Risk

[Benchmark Score]*SUM([Amount])

Let me know if this isn't what you're looking for.

Ricky