Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Product | Source | Destination | Cost | Cost 2 | Total Cost |
---|---|---|---|---|---|
Handbag | A | A1 | 0 | 5 | 5 |
Handbag2 | B | B1 | 10 | 10 | 20 |
Handbag3 | C | C1 | 20 | 15 | 35 |
Handbag4 | D | D1 | 30 | 10 | 40 |
Handbag5 | E | E1 | 40 | 20 | 60 |
Right now, I'm using the above example where I'm using =FirstSortedValue(Source, [Total Cost],1)
Is there any way that if my data had 0 as one of the cost, not to include it as the cheapest source?
For example, the cheapest source would be B, C, and D.
Based on Simen's suggestion this is more straightforward.
=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])
=IF(FirstSortedValue(Source, [Cost],1) = 0 or FirstSortedValue(Source, [Cost2],1) = 0, FirstSortedValue(Source, [Total Cost],2), FirstSortedValue(Source, [Total Cost],1))
This doesn't seem to work properly... it gives me the same value as the =FirstSortedValue(Source, [Total Cost],1)) even though the cost2 is 0. Any other ideas?
IF(FirstSortedValue(aggr(Sum(Cost),Source),Cost) = 0 or FirstSortedValue(aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,
FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost],2),FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost]))
This gives me the value. What do I do to get the field associated with that value? For example, instead of 20 in your example. I should see Handbag2.
IF(FirstSortedValue(Cost,aggr(Sum(Cost),Source)) = 0 or FirstSortedValue([Cost 2],aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,
FirstSortedValue(Product,aggr(Sum([Total Cost]),Product),2),FirstSortedValue(Product,aggr(Sum([Total Cost]),Product)))
Firstsortedvalue takes set analysis.
How about:
=FirstSortedValue({$<Cost={">0"}>}Source, [Total Cost],1)
Regards
SKG
Based on Simen's suggestion this is more straightforward.
=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])
=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost],1) seems to do the trick.
Now how do I find the Cost and Cost 2 associated with that? What is the right calculation to get the Cost and Cost 2?
From Calculation above:
cheapest:
Product, Source, Destination, Cost, Cost 2, Total Cost
Handbag2, B1, 10, 10, 20
=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])
Replace Product in bold with what field you want to grab. You Don't need the 1 at the end because it is implied if left out.