topic Re: Set modifiers for variable-based difference comparison in QlikView

Set modifiers for variable-based difference comparison

jwaligora — Tue, 25 Sep 2018 21:09:31 GMT

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!

Re: Set modifiers for variable-based difference comparison

swuehl — Tue, 25 Sep 2018 21:25:17 GMT

Try something like

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

Re: Set modifiers for variable-based difference comparison

swuehl — Tue, 25 Sep 2018 21:27:11 GMT

Or maybe

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

Re: Set modifiers for variable-based difference comparison

jwaligora — Thu, 27 Sep 2018 20:27:24 GMT

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!

Re: Set modifiers for variable-based difference comparison

jwaligora — Thu, 27 Sep 2018 20:31:24 GMT

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