Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having below table
ITEM | CITY 01 | CITY 02 | CITY 03 |
ITEM 01 | 10 | 20 | 13 |
ITEM 02 | 8 | 12 | 12 |
ITEM 03 | 24 | 20 | 14 |
ITEM 04 | 13 | 15 | 20 |
ITEM 05 | 18 | 14 | 14 |
ITEM 06 | 20 | 16 | 24 |
ITEM 07 | 24 | 18 | 13 |
ITEM 08 | 26 | 20 | 12 |
Need answer as below showing MINIMUM VALUE against EACH line.
ITEM | CITY 01 | CITY 02 | CITY 03 | MIN PRICE | CITY HAVING MIN PRICE |
ITEM 01 | 10 | 20 | 13 | 10 | CITY 01 |
ITEM 02 | 8 | 12 | 12 | 8 | CITY 01 |
ITEM 03 | 24 | 20 | 14 | 14 | CITY 03 |
ITEM 04 | 13 | 15 | 20 | 13 | CITY 01 |
ITEM 05 | 18 | 14 | 14 | 14 | CITY 02, CITY 03 |
ITEM 06 | 20 | 16 | 24 | 16 | CITY 02 |
ITEM 07 | 24 | 18 | 13 | 13 | CITY 03 |
ITEM 08 | 26 | 20 | 12 | 12 | CITY 03 |
Thanks in advance...
Regards,
Manish
Is CITY a dimension with CITY01, CITY02, CITY03 as values?
Can you post your app?
Dear Jason, CITY1, CITY2, CITY3 are dimensions.
In that case you would be best to crosstable the data. Can you post some sample data and your app so far? Easier to help then.
Hi,
You can try with this,
Have a straight table with the dimensions item, city 01, city 02, city 03
with expression
Min Price = RangeMin([CITY 01],[CITY 02],[CITY 03])
City having Min Price = If([CITY 01] = Column(1), 'CITY 01' & if([CITY 02] = Column(1), ',CITY 02', If([CITY 03] = Column(1), ',CITY 03','')), If([CITY 02] = Column(1), 'CITY 02' & if([CITY 03] = Column(1), ',CITY 03'),
'CITY 03'))
Hope it helps
Celambarasan
Dear Celambarasan,
Appriciate the way you have answered but I have around 30 to 40 CITY dimensions out of which I have given only 3.
Is there any another way.
Regards,
Manish
Hi,
Getting the field name of min value from the 40 dimensions is tedious one because as of me there is no method to get the field name based on the value. If you got any other help let me know.
Celambarasan
Hi Manish,
The attached uses Crosstable() to get however many CITIES there are into a tall thin table. It is then possible to get the minimum price into a chart, which I have done. The only thing I haven't got working yet is the concatenated string of cities that have the minimum price for each item. I know you need to use Concat() but I can't get it to limit the values...I'll have another go later.
Anyway - hope this helps.
Jason
Thanks for your reply...
Hi Manish,
I've managed to complete this for you by adding another bit to the script. The MinPrice will not be dependent upon other selections by doing it this way but maybe that's OK for you...
Hope it helps,
Jason