topic Re: set analysis on non-consecutive dates in QlikView

set analysis on non-consecutive dates

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

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

I am 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.

Any idea?

Thank you

Re: set analysis on non-consecutive dates

Anil_Babu_Samineni — Thu, 26 Oct 2017 10:09:36 GMT

Perhaps this?

Count({<Score = {">=5 <=10"}>} Name)

Count({<Score = {">=5 <=10"}, [Test Date] = {'Date1','Date2','Date3'}>} Name)

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 10:13:44 GMT

That is not taking into account the non consecutive dates business - or will need me to put all the dates manually into it - so no good, but thank you for trying

Re: set analysis on non-consecutive dates

Anil_Babu_Samineni — Thu, 26 Oct 2017 10:18:45 GMT

I apologies, expected result?

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 10:28:24 GMT

new dataset:

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

expected answer:

text object :2

straight table:

S1	yes
S2	no
S3	yes

Re: set analysis on non-consecutive dates

surendraj — Thu, 26 Oct 2017 10:51:11 GMT

You can try Expression as

aggr(if(Count({<Score = {">=5 <=10"}>} Name)>=5,'Yes','No'),Name)

--Surendra

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:02:56 GMT

Thank you

Re: set analysis on non-consecutive dates

techvarun — Thu, 26 Oct 2017 11:09:53 GMT

Check the attached.

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:14:39 GMT

actually that does not give correct answer - does not take care of the consecutive dates, it is a count on all date... but thank you for trying

I am 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.

Re: set analysis on non-consecutive dates

surendraj — Thu, 26 Oct 2017 11:17:45 GMT

Its working..You can Apply Order by while pulling the data!!

Re: set analysis on non-consecutive dates

sunny_talwar — Thu, 26 Oct 2017 11:23:41 GMT

Try this

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

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:26:58 GMT

I got feeling right but maybe a typo somewhere "Error in expression: ')' expected"?

Re: set analysis on non-consecutive dates

sunny_talwar — Thu, 26 Oct 2017 11:32:33 GMT

Are you using QV12 or above? If not, then it might not work for you because sorting in Aggr() function was made available after QV12 was launched. If you don't have QV12, you will have to make sure that Test Date is sorted in ascending order in script

Re: set analysis on non-consecutive dates

sunny_talwar — Thu, 26 Oct 2017 11:33:21 GMT

The expression though shows an error, gives the output like this

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:39:08 GMT

as always you are right I am using 11, many thanks

Re: set analysis on non-consecutive dates

sunny_talwar — Thu, 26 Oct 2017 11:40:25 GMT

Did it work then by removing the sorting from Aggr?

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:43:13 GMT

nor sure which part I need to remove, if you can give me the expression .

Re: set analysis on non-consecutive dates

sunny_talwar — Thu, 26 Oct 2017 11:45:05 GMT

This

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

But make sure that Test Date is sorted in ascending order for this expression to work

Re: set analysis on non-consecutive dates

Anonymous — Thu, 26 Oct 2017 11:47:33 GMT

Working perfectly, many thanks