topic Re: determine trends across multiple rows in QlikView

determine trends across multiple rows

Wed, 25 Nov 2020 16:16:04 GMT

Here's my sample data...

Load * Inline [

Exercise, ID, Result

Exercise 1, ABCD, selected

Exercise 1, QRST, selected

Exercise 1, LMNO, selected

Exercise 2, ABCD, no selection

Exercise 2, QRST, selected

Exercise 2, LMNO, selected

Exercise 3, ABCD, selected

Exercise 3, QRST, no selection

Exercise 3, LMNO, selected

];

I need to be able to show trends by ID. Additionally I want each 'no selection' to reset the counter.

For example the required results would be…

ABCD 1 selected

QRST 0 selected

LMNO 3 selected

Is this doable?

Thanks.

Re: determine trends across multiple rows

Anil_Babu_Samineni — Tue, 27 Jun 2017 05:49:17 GMT

How you are calculating the result set? Will you able to explain little more

Re: determine trends across multiple rows

Tue, 27 Jun 2017 12:41:23 GMT

At this point I was thinking of a simple chart...

ID	Exercise 1	Exercise 2	Exercise 3	Trend
ABCD	selected	no selection	selected	1 selected
QRST	selected	selected	no selection	0 selected
LMNO	selected	selected	selected	3 selected

Re: determine trends across multiple rows

antoniotiman — Tue, 27 Jun 2017 13:26:12 GMT

Hi Scott,

see Attachment.

Regards,

Antonio.

Re: determine trends across multiple rows

effinty2112 — Tue, 27 Jun 2017 13:28:48 GMT

Hi Scott,

This script:

TableA:

Load

Exercise,

ID,

if (Result = 'selected',Dual('selected',1),Dual('no selection',0)) as Result;

Load * Inline [

Exercise, ID, Result

Exercise 1, ABCD, selected

Exercise 1, QRST, selected

Exercise 1, LMNO, selected

Exercise 2, ABCD, no selection

Exercise 2, QRST, selected

Exercise 2, LMNO, selected

Exercise 3, ABCD, selected

Exercise 3, QRST, no selection

Exercise 3, LMNO, selected

];

NoConcatenate

TableB: LOAD * Resident TableA Order by ID,Exercise;

Drop Table TableA;

NoConcatenate

TableC:

LOAD

Exercise, ID, Result,

AutoNumber(Exercise,ID) as ExID,

if(ID=Peek(ID),Result*rangesum(Result, num(Peek(AccResult))),Result) as AccResult

Resident TableB;

Drop Table TableB;

will allow you to create this table:

ID	FirstSortedValue(Num(AccResult),-ExID)
ABCD	1
LMNO	3
QRST	0

Cheers

Andrew

Re: determine trends across multiple rows

Tue, 27 Jun 2017 19:45:53 GMT

That's perfect. Exactly what I was looking for. One more addition that would be nice... is there a way to allow a user to select on the totals? Given the results above, if I had a selection for the user and they selected '3', they would get the result set filtered down to just LMNO. If they selected '1', they would get ABCD. Possible?

Re: determine trends across multiple rows

antoniotiman — Tue, 27 Jun 2017 20:42:45 GMT

Yes, if I click in TOTAL 3 then

Re: determine trends across multiple rows

Tue, 27 Jun 2017 22:12:59 GMT

Super Antonio! One last question. If I wanted to eventually change to count the total number of 'selected' for an ID, would I be able to keep the basic logic and just tweak it for that?

Re: determine trends across multiple rows

antoniotiman — Tue, 27 Jun 2017 22:20:07 GMT

Yes, You ca.

Regards,

Antonio

Re: determine trends across multiple rows

Wed, 28 Jun 2017 02:00:01 GMT

Thanks Antonio. How much would the load change if they went that direction?