How to Return single row with maximum value

[#1]

Hi,

I have a chart table in which I want to show only students who are currently interrupted in their study. Each student has a number of terms they are registered in, and they may be either registered or interrupted in any one of them. I already have an indicator of whether student is currently interrupted, however I want to show as a dimension the term in which they are currently interrupted. Some students may have been interrupted multiple times over their history but I want to only show the latest interrupted term details. I attach a qvw with simple sample data and a table which is currently bringing back every row that the student has been interrupted in when I want it to show the maximum term row.

Can anyone help. I presume it requires some degree of aggregation in the set analysis but I can't get it right.

[IAMDV]

Hi,

I can't see the QVW file. I can see the Excelfile. Please explain what does these fields represent in Excel or share the sample QVW file?

Thanks,

DV

www.QlikShare.com

[#3]

Hi Deepak, apologies not thinking straight there!

I attach a qvw, however this chart shows multiple lines for pidm number 1, 1 row for each term code, when I only want to show the maximum term code for that pidm.

Thanks, Heather.

[perumal_41]

Hi,I have attached simple application .this useful for u .

Regards

Perumal A

[#5]

Hi Perumal A,

That’s great. It was nearly what I wanted. You had put

=Aggr(max(term),pidm)

And I wanted to get the maximum term for which the student’s registration code was ‘IN’ so I changed it to:

=Aggr(max({$<code={'IN'}>}term),pidm)

Thanks, Heather.

[#6]

Hi there,

I guess that I posted a much simplified example of what I was trying to do when in fact the data and UI is much more complex.

I have added another field to the excel sheet and script called programmeyear, i.e. the year the student was in during that term of study. When the user clicks on the programmeyear list box, the table should show only those students who were in that year of study during the maximum term for which their registration code was ‘IN’. So if no selection is made in programmeyear then the 'sum' table should show as it does now. If the user selects programmeyear 1 then only pidm 3 should show with maxterm = 200910. If user selects programmeyear 2 then noone shows. If user selects programmeyear 3 then only pidm 1 shows with maxterm = 201210.

If you have any further ideas on this I would be very grateful. I have tried to embed the aggregation function into a calculated dimension for programmeyear and also tried to embed it into the chart expression but having no luch so far.

Attached is the new excel file and qvw