Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti
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
sunny_talwar

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)

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

swuehl
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]

     )

)

sunny_talwar

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?

jonathandienst

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

It gives me null as expression result.

agigliotti
Partner
Partner

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

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