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?

[hopkinsc]

So to answer your question, Yes, i would need to sum up the stock levels for multiple Store Date entries for the same store date..

How would i write that?

[swuehl]

Well, try this:

=if(Date(aggr(NODISTINCT max(StoreDate),ProductCode,StoreNumber))=StoreDate,sum(StockLevel))

Where I used ProductCode and StoreNumber as dimensions in chart.

It picked the latest StoreDate for each combination of ProductCode and StoreNumber and compared to StoreDate of the charts row. If equal, get back sum of StockLevel (so summing up multiple StoreDate entries for the same day. Worked for me.

Stefan

[hopkinsc]

Hi Stefan, That expression still shows duplicate values but with '-' as a stock level. i have attached a sample again (as i have taken out the table for LastSoldDate) incase it's something i have done wrong...

[swuehl]

Hi Chris,

please check "supress zero values" on presentation tab.

[hopkinsc]

As simple as that!! Thanks very much, it looks promising. I have just created a report from one of our customers and will compare the data this afternoon.

Thanks for all your help Stefan...

[swuehl · Accepted solution]

Chris, wait.

Suppressing the zeros will also eliminate the rows with StockLevel = zero or 0.

I think that's not what you or your customers need.

To get rid of the duplicate rows, you must remove the StoreDate from the dimensions (but you can add it to the expressions). In my opinion, you should only use the fields as dimension that you need to group your data. Could be StoreNumber(or Customer) and ProductNumber.

Then, my expression is not correct, we must put the sum around the if-clause:

=sum(if(Date(aggr(NODISTINCT max(StoreDate),[Product Code],Customer))=StoreDate,StockLevel))

Looks much better to me.

Regards,

Stefan

edit: If you add StoreDate as expression, you need a

=Date(max(StoreDate))

, to only show the max StoreDate, else you get a '-' on rows with multiple StoreDates.

edited by swuehl

[hopkinsc]

Hi Stefan, just to let you know that this app now works great...Thanks

[swuehl]

Good to hear!

Have you applied my latest comments to the app?

If the issue is solved to your complete satisfaction, you might want to set this thread's status to answered, so everybody knows that here is no more work needed.

Regards,

Stefan