topic Re: Set analysis with Max function in QlikView

Set analysis with Max function

agigliotti — Wed, 03 Feb 2016 14:20:50 GMT

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

Re: Set analysis with Max function

sunny_talwar — Wed, 03 Feb 2016 14:23:21 GMT

May be this:

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

Re: Set analysis with Max function

Wed, 03 Feb 2016 14:30:50 GMT

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

agigliotti — Wed, 03 Feb 2016 14:31:20 GMT

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

swuehl — Wed, 03 Feb 2016 14:33:33 GMT

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

sunny_talwar — Wed, 03 Feb 2016 14:34:28 GMT

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

jonathandienst — Wed, 03 Feb 2016 14:36:38 GMT

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)

Re: Set analysis with Max function

agigliotti — Wed, 03 Feb 2016 14:41:07 GMT

It gives me null as expression result.

Re: Set analysis with Max function

agigliotti — Wed, 03 Feb 2016 14:44:36 GMT

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

Re: Set analysis with Max function

swuehl — Wed, 03 Feb 2016 14:45:40 GMT

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

sunny_talwar — Wed, 03 Feb 2016 14:46:24 GMT

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

jonathandienst — Wed, 03 Feb 2016 14:49:22 GMT

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

swuehl — Wed, 03 Feb 2016 14:55:34 GMT

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

agigliotti — Wed, 03 Feb 2016 15:11:28 GMT

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

swuehl — Wed, 03 Feb 2016 15:15:12 GMT

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

agigliotti — Wed, 03 Feb 2016 15:27:47 GMT

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

Re: Set analysis with Max function

swuehl — Wed, 03 Feb 2016 15:29:31 GMT

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

agigliotti — Wed, 03 Feb 2016 15:46:11 GMT

attached the qvw document with partial data.

to open the document:

user = qlik

pass = view

please let me know.

Thanks.

Re: Set analysis with Max function

swuehl — Wed, 03 Feb 2016 15:50:47 GMT

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

Re: Set analysis with Max function

agigliotti — Wed, 03 Feb 2016 16:08:08 GMT

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.