Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Sum({<Field1={"SALES"}, Field2={"EEEE"}, DeliveryDate = {"$(=Max(DeliveryData))"}>} Quantity)
You can't combine write IF condition inside Set Analysis. Try like below:
Sum( {< Field1={"SALES"}, Field2={"EEEE"} >} ,DeliveryDate = {"$(=Max(DeliveryDate))"} >} Quantity)
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.
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)
It gives me null as expression result.
It gives me "Error in expression" message in edit expression and null as expression result.
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> ..