Help with expression for showing latest details

[hopkinsc]

Hi, i am trying to display the current stock level for every product (for the selected department) for every store.

I have had some help with this before but still can't get it right.

My chart currently shows multiple lines for most of the products (this is because every store sends in a file every night detailing their stock details, so QLIKVIEW is showing me every occurence of the product). I only want to see the last file date that was processed (the field is StoreDate).

I want to see 1 line for each product showing the last file processed with the latest LastSoldDate but it has to display the accurate StockLevel for each product.

I have attached a sample.

Can anyone help please?

[SunilChauhan]

at what basis  u want to see this field Please make a clear requirement.

Thanks

Sunil

[hopkinsc]

The ACCURATE stock level would be the one with the latest StoreDate and latest LastSoldDate.

In the above example, the correct line is the 2nd one down. This is the only line that i want it to display...

(It has the latest StoreDate with the Latest LastSoldDate)

[SunilChauhan]

TAKE MAX OF BOTH DATE IN SCRIPT GROUP BY AND RELOAD

I THINK WILL GIVE RESULT

[hopkinsc]

Sorry Sunil, can you give me an example as i don't know what you mean.

Thanks

[hopkinsc]

does anyone have any more suggestions please?

[SunilChauhan]

will u share data  for reload?

then only i will say u.

[hopkinsc]

Hi, my QVD's are too big to attach for reload

[swuehl]

Hi, see my example in http://community.qlik.com/message/136954#136954

Stefan

[hopkinsc]

Hi, Stefan, Thanks that looks very promising!

I will put it onto the server and reload it and let you know

Thanks!

[hopkinsc]

Hi Stefan, That expression worked great, thanks very much.

I was wondering if you could break down the expression and explain what each part does so i can try and understand it ?

if(If(Max(Aggr(NODISTINCT Max(LastSoldDate),[Product Code], StoreNumber)) = LastSoldDate, LastSoldDate) = StoreDate, StockLevel)

Thanks