Set modifiers for variable-based difference comparison

[jwaligora]

Hi,

Simplified scenario:

I have 3 fields loaded {UniqueRecordID, EstimatedValue, ActualValue}. For sake of brevity let's call them {ID, EV, AV}.

I then need to introduce a user-modifiable buffer, vBuffer, that will define the acceptable margin of error around EV, such that if

EV-vBuffer < AV < EV+vBuffer

we are happy and otherwise we're not. In words: AV must fall within the (user controlled) buffer zone of EV.

The end game is to present the data in a stacked Bar Chart with series "Happy" and "Not Happy" counting the unique IDs for each category.

This sounds extremely simple, and hence must be possible.

In a table, I can get the desired categorization on a per-line basis, using an if() statement:

if ( (EV-vBuffer) < AV AND (EV+vBuffer) > AV,  'Happy', 'Not Happy' )

In the chart, I can use:

sum ( if ( (EV-vBuffer) < AV AND (EV+vBuffer) > AV,  1, 0 ) )

but that's a sum across all rows and not just the unique ones (I know that with the scenario I'm outlining, uniqueness could be ensured through other, simpler ways, but the real data set is rather complex, with multiple joined tables, and has to make allowances for for a long list of filters and calculations, so I need solid foundational logic).

Bottom line; I'm after a set modifier:

count (distinct {$ <Magic>} ID)

Tried a number of things, but failed miserably each time.

QV Nov 2017 SR5 | 12.20.20600.0

Thanks!

[swuehl]

Try something like

sum( Aggr( if ( (EV-vBuffer) < AV AND (EV+vBuffer) > AV,  1, 0 ), UniqueID))

[swuehl · Accepted solution]

Or maybe

=Count({<ID = {"= (EV-vBuffer) < AV AND (EV+vBuffer) > AV"}>} DISTINCT ID)

[jwaligora]

Bingo on both counts!

I marked the second one correct because I completely forgot about the Aggr() function, meanwhile I have zero excuse for not arriving at the second option sooner.

Thanks bunches!

[jwaligora]

Here's a sandbox for testing / playing with Stefan's solution.