topic Re: FirstSortedValue or set analysis in QlikView

FirstSortedValue or set analysis

paolojolly — Thu, 07 Feb 2013 08:09:44 GMT

Hi, I have this table

I need to create a pivot table with ProductType as dimension and in expression the value of status for the max(Date) of ProductType

I try to use

max({<Date={"$(=max(Date))"}>} Status)

but the result is not correct. So I try to use

FirstSortedValue(distinct Status, -Date)

But I'm not sure that it is the best solution.

Help me on this...urgent

Thank you

Re: FirstSortedValue or set analysis

SunilChauhan — Thu, 07 Feb 2013 08:40:11 GMT

use below in your Expression

Only({<Date={"$(=max(Date))"}>} Status)

hope this helps

Re: FirstSortedValue or set analysis

er_mohit — Thu, 07 Feb 2013 08:54:44 GMT

hiii

try this in script level

LOAD producttype,

ID,

max(Date)as maxDate,

FirstSortedValue(Status,-Date)as Status

group by producttype;

load ur table fields

from ......;

Re: FirstSortedValue or set analysis

Thu, 07 Feb 2013 09:20:48 GMT

try this

LOAD producttype,

date(max(date))as maxDate,

FirstSortedValue(status,-date)as Status

group by producttype;

LOAD auditnmame,

date,

id,

producttype,

status

FROM

C:\Users\vishwaranjan\Desktop\FIRSTSORTED.xlsx

(ooxml, embedded labels, table is Sheet1);

then output like this

producttype	maxDate	Status
AA	2/1/2013	BADX
BB	1/2/2012	GOOD
CC	5/1/2013	GOOD

Re: FirstSortedValue or set analysis

paolojolly — Thu, 07 Feb 2013 17:13:01 GMT

I'm sorry but this does not work

Re: FirstSortedValue or set analysis

paolojolly — Thu, 07 Feb 2013 18:42:06 GMT

Thanks, This solution help me, but now I have another problem. I have changed the table in

and I need to create a pivot table with ProductType as dimension and in expression the value of status for the max(Date) of ProductType and I need to use a list box for filter the information on SalesRep and another for Customer. For example If i select in Customer list box "Cust1" the pivot shoud give me for each ProductType the values of Status field for the max(Date), idem if I work with SalesRep listbox

Thanks