Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Bolijay00
Contributor III
Contributor III

Date range using Set analysis not spitting out correctly

Bolijay00_0-1708523592503.jpegBefore SelectionBefore SelectionAfter selection bringing the correct valueAfter 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 .
Labels (2)
2 Solutions

Accepted Solutions
LRuCelver
Partner - Creator
Partner - Creator

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:

LRuCelver_1-1708528737910.png

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;

 

View solution in original post

Bolijay00
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
LRuCelver
Partner - Creator
Partner - Creator

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:

LRuCelver_1-1708528737910.png

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
Contributor III
Contributor III
Author

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