Set analysis with Max function

[agigliotti]

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

[sunny_talwar]

May be this:

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

[#3]

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

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

[agigliotti]

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]

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)

[agigliotti]

It gives me null as expression result.

[agigliotti]

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

[swuehl]

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