Expression not working as expected

[hopkinsc]

Hi everyone. (I have also posted this in 'New to Qlikview as i wasn't sure which would be best)

I have an expression in the attached sample. It shows me the most up to date stock level of each product. For 95% of the products it works as expected, but i have been asked to check it again as some products are not being displayed.

I was wondering if anyone could look at the sample to see if you can see the reason why it doesnt display all products as expected.

What the expression should do, is look at the date of each record for each product and display the stock level for the latest date (and hide the others). So for 1 product, there will be multiple records each with different dates, The expression should then compare each date and only display the latest one.

There are 2 charts in the sample

Current Stock Levels

Current Stock Levels - Copy

The first chart is the original one.

The copy chart is the same as the first one but with the field StockLevel added also.

There is a product already selected (5099873015506 - Southern Comfort). There is nothing being displayed in the first chart but the second chart shows multiple records with different dates (which shows there are stock levels for that product). The first chart should be displaying the record dated 30/07/11 which shows a stock level of 7.

I don't know why it is not showing this record.

Can anyone help please?

I have other products if you want to test further..

5010327000404 which should display record dated 13/06/11

5010196065061 which should display record dated 07/08/11

Thanks very much

[Miguel_Angel_Baeyens · Accepted solution]

Hi Chris,

That If() may be returning more than one result of StockLevel per dimension value, and you need to aggregate them, so the following as the expression in the first chart instead of the actual should work

Sum(If(Date(Aggr(NODISTINCT Max(StoreDate), EAN, Customer)) = StoreDate, StockLevel))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: P.S.: This is not a newbie question, so as far as I'm concerned, this should be the right place to be.

[hopkinsc]

Hi Miguel, Thanks very much, that worked as expected.

Chris