Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 

Set analysis with Max function


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


21 Replies

May be this:

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

Not applicable

You can't combine write IF condition inside Set Analysis. Try like below:

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


it doesn't works because it shows me only latest customers deliveries ex. only deliveries of yesterday...

I need to display all customers with details regarding only the last delivery.


Set analysis won't regard the dimension line, i.e. customer.

Try either something like

Sum( {< Field1={"SALES"}, Field2={"EEEE"} >}


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

           , customer, [delivery number], [DeliveryDate]



May be create a flag in the script to flag the last delivery for each customer and then use that flag in your set analysis. For a more specific help, would you be able to provide a sample?

You can't bring the Max() into the set expression because you want the latest date per customer. You could try this:

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

(although I am not sure if you can use a partial total here)

What I would recommend is that you add a last delivery flag derived field during the load. for example, after loading the deliveries (into the [Deliveries] table in this example), add the following script fragment:

Left Join (Deliveries)

LOAD CustomerID,

  Max(DeliveryDate) As DeiveryDate,

  1 As LastDelivery

Resident Deliveries

Group By CustomerID;

Now your expression becomes

=Sum( {< Field1={"SALES"}, Field2={"EEEE"}, LastDelivery={1} >} Quantity)

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

It gives me null as expression result.


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


Have you checked that the aggr() dimension fields exactely match the fields used as your chart dimensions?

I may have misspelled your customer field in  ... Total<Customer> ... , try

.. Total<customer> ..