Finding the minium of an series of values

[#1]

Hi Qlik,

I have a list of sites that each do a series of deliveries to mulitple destinations.

I have a table set up with Source [Source Name] as the dimension showing the average cost avg[Total Cost] of delivery to each destination [Destination Name].

Looking at opportunity cost I want to establish as a varible what the cheapest site to ship to is. However merely entering min(Total Cost) gives me back the cheapest delievry overall rather than the average of the site with lowest cost.

Is there a way to create an array within the equation of average total cost to each site and then select the minimum average value?

Regards,

Chris

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this: min(aggr(avg([Total Cost]),[Destination Name], [Source Name]))

[#3]

Thank you Gysbert, worked like a charm.

[swuehl]

If your request is resolved, then please close this thread:

Qlik Community Tip: Marking Replies as Correct or Helpful

[#5]

As a follow up, I'm now trying to use this to find the next best alternative. So far I have:

if(count(distinct([Destination Name]))>1,

min({$<[Total Cost]={"<>'[Best route]'"}>} [Best Route]))

Where best route is the previous equation.

[swuehl]

In general, try to keep a thread to a single request.

Since it's quite similar, maybe

if(count(distinct([Destination Name]))>1,

min(aggr(avg([Total Cost]),[Destination Name], [Source Name]), 2)

)

[#7]

Apologies Swuehl, will start a new one next time.

Thank you your answer worked perfectly.