Calculation of percent of difference issue

Question & Answer
,
1

I am able to find the avgover using the following calculated field.

avgOver({current_balance}, [{screen_group}, tier1, tier2, tier3, tier4, tier5, {account - desc}], PRE_FILTER) ) .

However when I try to use this in formula below results do not generate expected results.
( {current_balance} - avgOver({current_balance}, [{screen_group}, tier1, tier2, tier3, tier4, tier5, {account - desc}], PRE_FILTER) )
/ abs(avgOver({current_balance}, [{screen_group}, tier1, tier2, tier3, tier4, tier5, {account - desc}], PRE_FILTER)) * 100

This should generate Calculate the Difference:

  1. Calculate the Difference: Difference=25,000−733.33=24,266.67
  2. Calculate the Percent Difference: Percent Difference=(733.33/24,266.67​)×100≈3,312.00%

However I see result of
image

I am confused as to why the avgover works as stand alone calculation but not in second calculated field.

2

@Sanders77 , not sure, but probably you see a result of aggregation. In plain words, if this result is collected from many rows, you are aggregating precents instead of aggregating {current_balance}. If there are many rows with very small amounts it probably could affect results, but the difference seems to be too big for this. Anyway, you could try something like this:

( sum({current_balance}) - avg( avgOver(..) ) / abs( avg( avgOver(..) ) )

You can use min() or max() instead of avg(), it does not matter because the value should be the same in all rows within a window.

Optional: No need to do *100, just use the proper format: “Show as: PERCENT” instead of “Show as: NUMBER”.

3

afraid adding sum to current_balance results in error below.

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

You are correct on dropping the * 100 at end and change to percent. However afraid that still leaves question of why calculation is different from one calculated field is different when same algorithm is used in larger calculated field.

4

Still looking for help understanding why results are not as expected.

5

Hello @Sanders77

Are you still working through this issue or were you able to find a solution?

The error happens because of the LAC functions. To troubleshoot could you try sumOver({current_balance}, [], PRE_FILTER). Let me know if that gives a new error or the same error.

For nesting aggregations I recommend checking out this blog from a community expert: