How do i write set analysis??

[#1]

Hi All,

for example this is my data.

     item   Date             ord_no        Rate

     123     2011-jan-17        1             22

     123     2011-mar-18       2             12

     145     2011-feb-19        3             30

     145     2011-mar-20       4            13

     165     2011-jan-30        5             15

     165     2011-mar-01       6            17

     165     2011-mar-31        7           255

if i select financial year like 2011-2012 i need last year means upto 31-3-11 max ord_no corresponding rate.

item wise.

My result like this i need:

item         rate

123          12

145           13

165            255

Thanks,

Ashok.

[sujeetsingh]

Just go with following link and it will help you to set analysis...

http://community.qlik.com/servlet/JiveServlet/previewBody/1867-102-1-1858/QlikLearn-Set-Analysis-Pre...

[jagan]

Hi Ashok,

Hope attached file helps you. 

Regards,

Jagan.

[CELAMBARASAN]

First Sorted value help you here.

Item as dimension

=FirstSortedValue(Rate, -OrdNo)

Hope it helps

[#5]

Hi,

      that function is working for current year but i need last year max date rate.

[#6]

see this link

http://community.qlik.com/thread/64070

[er_mohit]

-FirstSortedValue(OrdNo,-Rate)

hope it helps you

[CELAMBARASAN · Accepted solution]

Then use set analysis in that function

=FirstSortedValue({<YearField={$(=Max(YearField)-1)}>} Rate, -OrdNo)

[#9]

Hi,

    but it's giving all zero's.

[#10]

Hi,

   Thanks,it's working perfectly.