topic Re: above on calculated field in QlikView

above on calculated field

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

LOAD * INLINE [
Test Date, Name, Score, Bonus
01/01/2017, S1, 9,3
01/01/2017, S2, 10,1
01/01/2017, S3, 6,2
17/03/2017, S1, 9,2
17/03/2017, S2, 7,2
17/03/2017, S3, 7,2
03/05/2017, S1, 4,2
03/05/2017, S2, 1,2
03/05/2017, S3, 2,2
30/05/2017, S1, 8,2
30/05/2017, S2, 1,2
30/05/2017, S3, 8,2
31/05/2017, S1, 5,10
31/05/2017, S2, 1,10
31/05/2017, S3, 10,10
01/10/2017, S1, 5,10
01/10/2017, S2, 10,5
01/10/2017, S3, 9,5
];

I was trying to figure out how many students manage to keep a consecutive score between 5 and 10 for 3 consecutive test date, to show that in both a text object , and also in a straight table.

Thank to Sunny (Re: set analysis on non-consecutive dates) I can now using the following expression:

=Count(DISTINCT Aggr(If(RangeMin(Above(Score, 0, 3)) >= 5 and RangeMax(Above(Score, 0, 3)) <= 10 and RowNo() > 2, Name), Name, [Test Date]))

now I need to do the same but instead of Score to do it on (Score+Bonus)

The above will not work, if I replace Score with (Score+Bonus) then only way around I have found so far is to create a new field in the script that has the value of (Score+Bonus) and then use it instead of Score in the Expression, but I would not not to do that via script, any other way to do it?

Re: above on calculated field

sunny_talwar — Tue, 31 Oct 2017 14:46:24 GMT

I think the reason it doesn't work is because your range is still 5 to 10.... shouldn't the range increase because now we are looking at Score + Bonus? For example, if I change the range from 5 to 15, I see S1 with a count of 1

=Count(DISTINCT Aggr(If(RangeMin(Above(Score+Bonus, 0, 3)) >= 5 and RangeMax(Above(Score+Bonus, 0, 3)) <= 15 and RowNo() > 2, Name), Name, [Test Date]))

Re: above on calculated field

Anonymous — Tue, 31 Oct 2017 14:50:03 GMT

you are right as usual, additionally is there a way to ignore any Test Date before a specific date lets say 01/05/1017

Re: above on calculated field

sunny_talwar — Tue, 31 Oct 2017 14:53:39 GMT

Not entirely sure, but try this

=Count(DISTINCT Aggr(If(RangeMin(Above(Only({<[Test Date] = {">$(=Date(MakeDate(2017, 5, 1)))"}>}Score+Bonus), 0, 3)) >= 5 and RangeMax(Above(Only({<[Test Date] = {">$(=Date(MakeDate(2017, 5, 1)))"}>}Score+Bonus), 0, 3)) <= 20 and RowNo() > 2, Name), Name, [Test Date]))

Re: above on calculated field

Anonymous — Tue, 31 Oct 2017 14:54:58 GMT

thank you