Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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

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)

Partner
Partner

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.

MVP
MVP

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

Try either something like

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

     Aggr(

           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
Partner
Partner

It gives me null as expression result.

Partner
Partner

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

MVP
MVP

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> ..