How do i write expression in straight table?

[#1]

Hi All,

       for example this is my data.

     item   Date             ord_no        Rate

     123     2011-01-17      1            12

     123     2011-01-18       2            28

     145     2011-01-19      3             23

     145     2011-01-20      4             30

     165     2011-01-30      5             15

     165     2011-02-01      6              17

     165     2011-01-31      7              25

I need result like this in straight table:

  item   Date             ord_no        maxrate

   123     2011-01-18       2            28

    145     2011-01-20      4             30

    165     2011-01-31      7              25

[Gysbert_Wassenaar · Accepted solution]

You can use the firstsortedvalue function. Use item as dimension and three expressions:

Date: FirstSortedValue(Date, -Rate)

ord_no: FirstSortedValue(ord_no, -Rate)

maxrate: FirstSortedValue(Rate, -Rate)

You can also use max(Rate) as expression for maxrate.

[vikasgupta]

Hi Ashok

Please find the attached solution for same . here just use dimesnion Order no and use Max(Rate) function in expression.

Thanks

Vikas

[#4]

Hi Wassenaar,

                  Thanks alot it's working.

i have one more query that is if i select financial year like 2011-2012 i need last year means upto 31-3-11 max ord_no corresponding rate.how do i write expression in striaght table????

like this :

              item      rate

              123        45

              134       55

Thanks in advace,

Ashok.

[#5]

hi Ashok,

You can use set analysis for your second query.

hope it will help.

-Anil

[#6]

Hi Anil,

           I used set analysis but i am not getting correct result.

I used:= sum({$<FinancialYear,MonthName,YearMonth={$(=min(YearMonth)-1)}>}sld_rate).

suggest me.

Thanks,

Ashok.

[#7]

hi wait,I m in a Biz meeting.

-Anil

[#8]

hi Still in meeting will you send me the sample data, try to reply soon but how soon cant say.

-Anil

[#9]

Hi Anil,

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