Date range using Set analysis not spitting out correctly

[Bolijay00]

Before SelectionAfter selection bringing the correct value

Hi guys

i am using ths formular in a table in Qlik Coalesce( Avg({< [Collection date] = {">=$(=Monthstart(min([Change date]),-6)))<=$(=Monthstart(mIN([Change date])))"} >} ANB), 0), but the table has differrent change date for different machine, i discoverd that figures showed on the table is as per minimum changedate for the entire entries until you clik on a particular entriy then it shows the asssociated change date for that entry, but the figures displayed on the full table is not unique to each entry individually but the the overall using the minimum change date for the entire change date., how do i resolve this such that the entire table would be showing caculations pertaining to each entries rather than as a collective .

[LRuCelver · Accepted solution]

Set expressions are evaluated on the chart level, i.e. only once per chart.

You need to use e.g. an if statement in your calculation to filter for your upper and lower bounds.

Here is an example:

Incorrect Expression:

Sum({<Category={">=$(=Min(Lower)) <=$(=Max(Upper))"}>} Measure)

Correct Expression:

Sum(If(Category >= Lower and Category <= Upper, Measure))

Script:

Data:
NoConcatenate Load
 Ceil(Rand() * 3) as Category,
    RangeMin(Res1, Res2) as Lower,
    RangeMax(Res1, Res2) as Upper,
    1 as Measure;
Load
 Ceil(Rand() * 3) as Res1,
 Ceil(Rand() * 3) as Res2
AutoGenerate 1000;

[Bolijay00 · Accepted solution]

thak You very much for your guidiance , you made me to use the if statement and it was just very simple - Avg(
If(
[Collection date] >= AddMonths([Change date], -6) and [Collection date] <= [Change date],
ANB
)
)

and it worked just fine.
cheers