Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti
Partner - Champion
Partner - Champion

Set analysis with Max function

Hello,

I'm stuck with the below expression:

Sum( {< Field1={"SALES"}, Field2={"EEEE"} >} if( DeliveryDate = Max(DeliveryDate), Quantity, 0 ) )

using the following dimensions: customer, delivery number, delivery date

I need to SUM only the items sold in the last delivery for each customer.

How can I achieve it ?

Please help me.

Best Regards

Andrea

21 Replies
sunny_talwar

Is the field name called DeliveryDate (e in the end) or DeliveryData (a in the end). Make sure you are just not copy pasting and using the right field names.

jonathandienst
Partner - Champion III
Partner - Champion III

Andrea Gigliotti wrote:

It gives me "Error in expression" message in edit expression and null as expression result.

Well I was not sure whether that would work. You can use Max(TOTAL...) like that but perhaps not a partial total, but that would be the same as putting it in the set expression.

Try to debug swuehl's expression or add the derived field as I suggested before.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swuehl
MVP
MVP

A LastDelivery flag would indeed be best, if your filter does not need to take selections into account, e.g. on Product.

agigliotti
Partner - Champion
Partner - Champion
Author

maybe i made a mistake with the syntax however it doesn't works because many customers are missing and also I get more then 1 delivery for some customers.

Definitely I think adding a new field as Jonathan suggested me is the best solution.

swuehl
MVP
MVP

If you get more than 1 delivery for some customers, do you have multiple deliveries on the same date for these customers? Maybe you shouldn't compare delivery dates but a deliver ID, e.g. [delivery number]?

agigliotti
Partner - Champion
Partner - Champion
Author

however it would be great to be able to achieve the same result using a complex chart expression.

swuehl
MVP
MVP

This should be possible.

Could you create a small sample QVW? It's hard to debug the expression without knowing your data model, field values etc.

agigliotti
Partner - Champion
Partner - Champion
Author

attached the qvw document with partial data.

to open the document:

user = qlik

pass = view

please let me know.

Thanks.

swuehl
MVP
MVP

Could you point me to customers that are missing or that show more than one delivery?

agigliotti
Partner - Champion
Partner - Champion
Author

in your partial data you can't see that cases.

however it's seldom to have more deliveries on the same date for the same customer.

I'll let you know if in both ways (expression and by a new field) I get the same result.