21 Replies Latest reply: Feb 4, 2016 3:51 AM by Andrea Gigliotti

# 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 ?

Best Regards

Andrea

• ###### Re: Set analysis with Max function

May be this:

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

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

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?

• ###### Re: Set analysis with Max function

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

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

• ###### Re: Set analysis with Max function

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]

)

)

• ###### Re: Set analysis with Max function

It gives me null as expression result.

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

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)

Max(DeliveryDate) As DeiveryDate,

1 As LastDelivery

Resident Deliveries

Group By CustomerID;

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

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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

• ###### Re: Set analysis with Max function

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.

• ###### Re: Set analysis with Max function

attached the qvw document with partial data.

to open the document:

user = qlik

pass = view

Thanks.

• ###### Re: Set analysis with Max function

Hi Andrea,

I have tested this expression on your qvw

`RangeSum(If([%Data] = Max({\$< %TipoVenditaLink={"VENDITA"}, DirettoGrossista={"Diretto"}, CategoriaArticoloID={"122"}, Vendite.Tipo.Documento -= {"RESI"} >} TOTAL<Cliente> %Data),sum(Qta),0))`

Regards,

Ely

• ###### Re: Set analysis with Max function

Hi Ely,

I'll let you know asap if your suggested expression give me the right result.