Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
A LastDelivery flag would indeed be best, if your filter does not need to take selections into account, e.g. on Product.
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.
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]?
however it would be great to be able to achieve the same result using a complex chart expression.
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.
attached the qvw document with partial data.
to open the document:
user = qlik
pass = view
please let me know.
Thanks.
Could you point me to customers that are missing or that show more than one delivery?
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.