determine trends across multiple rows

[#1]

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.

[Anil_Babu_Samineni]

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

[#3]

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

[antoniotiman · Accepted solution]

Hi Scott,

see Attachment.

Regards,

Antonio.

[effinty2112]

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

[#6]

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?

[antoniotiman]

Yes, if I click in TOTAL 3 then

[#8]

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?

[antoniotiman]

Yes, You ca.

Regards,

Antonio

[#10]

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