Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I'm kind of new to using this forum but I'm drawing blanks here and really hope that someone can get me on the right path.
I have a table with competitor prices on products. Some products are sold by more than one competitior. I wish to create a new table showing only the row (competitor) with the cheapest price. I figured out how to show the lowest price but I can't figure out how to show the competitor based on the CompetitorPrice column
First Table
ItemNo | Rank | Competitor | Index | Competitor Price | Our Price | Diff | Our Stock | Competitor |
188679 | 19 | Alpha | 0,98 | 1 690 | 1 729 | -39 | In Stock | In Stock |
188679 | 19 | Beta | 1,16 | 1 999 | 1 729 | 270 | In Stock | In Stock |
188679 | 19 | Gamma | 1,61 | 2 790 | 1 729 | 1061 | In Stock | In Stock |
188679 | 19 | Delta | 1,61 | 2 790 | 1 729 | 1061 | In Stock | In Stock |
188681 | 17 | Alpha | 1,00 | 2 580 | 2 585 | -5 | In Stock | In Stock |
188681 | 17 | Beta | 1,16 | 2 999 | 2 585 | 414 | In Stock | In Stock |
188681 | 17 | Gamma | 1,02 | 2 640 | 2 585 | 55 | In Stock | In Stock |
188643 | 3 | Alpha | 0,75 | 370 | 495 | -125 | In Stock | In Stock |
188643 | 3 | Delta | 1,11 | 549 | 495 | 54 | In Stock | In Stock |
188649 | 4 | Alpha | 1,00 | 689 | 689 | 0 | In Stock | In Stock |
188665 | 16 | Alpha | 0,97 | 1 155 | 1 195 | -40 | In Stock | In Stock |
188673 | 20 | Alpha | 0,98 | 1 550 | 1 579 | -29 | In Stock | In Stock |
188673 | 20 | Beta | 1,08 | 1 699 | 1 579 | 120 | In Stock | In Stock |
188673 | 20 | Gamma | 1,01 | 1 590 | 1 579 | 11 | In Stock | In Stock |
188673 | 20 | Delta | 1,39 | 2 190 | 1 579 | 611 | In Stock | In Stock |
Desired Table
ItemNo | Rank | Competitor | Index | Competitor Price | Our Price | Diff | Our Stock | Competitor |
188679 | 19 | 0,98 | 1690 | 1729 | -39 | In Stock | ||
188681 | 17 | 1,00 | 2580 | 2585 | -5 | In Stock | ||
188643 | 3 | 0,75 | 370 | 495 | -125 | In Stock | ||
188649 | 4 | 1,00 | 689 | 689 | 0 | In Stock | ||
188665 | 16 | 0,97 | 1155 | 1195 | -40 | In Stock | ||
188673 | 20 | 0,98 | 1550 | 1579 | -29 | In Stock |
Now,
Price and index columns is calculated on
aggr(Min({$<Competitor-={'OurStore'}>}$(vPrice)),Timestamp, ItemNo))
Where vPrice is a variable containing the price either with or without freight.
So how can I display the corresponding Competitor to that price?
If there are more than one competitor with the cheapest price it is ok to show a blank or any competitor with that price.
I need it to be dynamic and based on the competitors in my selection which is why I haven't just calculated it in the load script.
I'm very grateful for any ideas or suggestions.
Try the firstsortedvalue() expresssion. This returns the value to a corresponding minimum / maximum value in another field.
So in this case you would have the product as the dimension, then
=firstsortedvalue(Competitor,Price) to return the competitor associated with the lowest price.
Erica
I feel like if Competitor is a dimension in your chart, it should show the corresponding competitor for your chart for that price.
Could you give more info as to the structure of your table? which are dimensions and which are expressions?
May be maxString(Competitor) if its an expression?
Try the firstsortedvalue() expresssion. This returns the value to a corresponding minimum / maximum value in another field.
So in this case you would have the product as the dimension, then
=firstsortedvalue(Competitor,Price) to return the competitor associated with the lowest price.
Erica
Hi yes, Competitor is a Dimension in the first chart (is there a way for me to edit my post?)
I've tried max string but that just gives me a competitor that doesn't correspond to that row.
I wanted to try to use
({$<Price*={$(=(aggr(Min({$<Competitor-={OurStore}>}$(vPrice)),Timestamp, ItemNo)))}>}Competitor)
But that only worked when only one item was selected.
Thanks Erica, I actually tried the firstsortedvalue() but you are right it does seem to work.
FirstSortedValue(Competitor,$(vPrice))
And to remove 'OurStore'
FirstSortedValue({$<Competitor-={'OurStore'}>}Competitor,$(vPrice))
Thanks for quick replies