FirstSortedValue or set analysis

[paolojolly]

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

[SunilChauhan]

use below in your Expression

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

hope this helps

[er_mohit]

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 ......;

[#4]

hi

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

[paolojolly]

I'm sorry but this does not work

[paolojolly]

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