topic Re: How do i write expression in straight table? in QlikView

How do i write expression in straight table?

Sat, 22 Dec 2012 09:43:44 GMT

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

Re: How do i write expression in straight table?

Gysbert_Wassenaar — Sat, 22 Dec 2012 09:59:05 GMT

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.

Re: How do i write expression in straight table?

vikasgupta — Sun, 23 Dec 2012 12:50:42 GMT

Hi Ashok

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

Thanks

Vikas

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 04:46:30 GMT

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.

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 06:26:19 GMT

hi Ashok,

You can use set analysis for your second query.

hope it will help.

-Anil

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 06:38:09 GMT

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.

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 06:50:18 GMT

hi wait,I m in a Biz meeting.

-Anil

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 07:02:05 GMT

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

-Anil

Re: How do i write expression in straight table?

Mon, 24 Dec 2012 07:21:10 GMT

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