Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Hi Ashok
Please find the attached solution for same . here just use dimesnion Order no and use Max(Rate) function in expression.
Thanks
Vikas
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.
hi Ashok,
You can use set analysis for your second query.
hope it will help.
-Anil
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.
hi wait,I m in a Biz meeting.
-Anil
hi Still in meeting will you send me the sample data, try to reply soon but how soon cant say.
-Anil
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