Display OrderNo with cheapest Product

[Sello_Mputsoe]

Hi Everyone,

I have a table that shows a sales transactions on Year, OrderNo, ProductName and ProductID. The table consists of a "Best Price" column which displays the lowest Avg Price (Please note that this column's values are duplicated for every row as per the requirements.

I need to apply the same for the "Best OrderNo" column - display the order number that has the lowest Avg Price on every row on a straight table - possibly using the total  and Aggr functions.

Eg. Since order number 4510690216 has the lowest Avg price on this product, then the "Best OrderNo" column will be populated by 4510690216 for all rows as per below.

Your assistance will be highly appreciated!

[eddie_wagt]

If you want to display it, I assume you want to have a formula in the frontend. It is maybe better to script this in the load editor. Hence, here are the formulas.

Best Price

FirstSortedValue(total [Avg Price],aggr(avg([Avg Price]),[Avg Price]))

Best OrderNo

FirstSortedValue(total OrderNo,aggr(avg([Avg Price]),OrderNo))

[Sello_Mputsoe]

Hi Eddie, thanks for your suggested solution. Unfortunately it does not work. Basically what I need is to display an order number with the lowest avg price. Please refer to my attached dummy data file and screenshot above

[Sello_Mputsoe]

This is what I already have to calculate my best price and this seems to work fine.

min( total <ProductID> aggr(sum(SalesAmount)/sum( QTY),ProductID,ProductName ,OrderNo, Year))

Is there a way I can reuse this to get that OrderNo with the lowest best Price and display it like the below?

[eddie_wagt]

Hello @Sello_Mputsoe ,

I don't understand why it is not working. Are there other requirements? I loaded the dummy data and this is the result:

Regards Eddie

[Sello_Mputsoe]

Hi @eddie_wagt , this is exactly what I need. The only other requirement there is that we looking for the best order number within the previous 12 months only.

[eddie_wagt · Accepted solution]

Assuming you have yearmonth as a dimension you could do something like this

Best Price

FirstSortedValue(total [Avg Price],aggr(avg({1<YearMonth={">=$(=date(addmonths(max(YearMonth),-12),'YYYY-MM'))<=$(=date(max(YearMonth),'YYYY-MM'))"}>}[Avg Price]),[Avg Price]))

Best Order

FirstSortedValue(total OrderNo,aggr(avg({1<YearMonth={">=$(=date(addmonths(max(YearMonth),-12),'YYYY-MM'))<=$(=date(max(YearMonth),'YYYY-MM'))"}>}[Avg Price]),OrderNo))