topic Re: Date range using Set analysis not spitting out correctly in App Development

Date range using Set analysis not spitting out correctly

Bolijay00 — Fri, 15 Nov 2024 21:03:25 GMT

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 .

Re: Date range using Set analysis not spitting out correctly

LRuCelver — Wed, 21 Feb 2024 15:19:39 GMT

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;

Re: Date range using Set analysis not spitting out correctly

Bolijay00 — Thu, 22 Feb 2024 08:58:40 GMT

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