max(description) in Dimension

[#1]

Hi

I have loaded a product detail reference table which contains productcode, productdesc and productmastercode as well as a field I made in the load from productdisplay which is the productcode & '-' & productdesc.  There can be a one to many relationship between productmastercode and productcode.

I have built a pivot table in which I have as dimensions product hierarchy then productmastercode then productdisplay and this works fine.  However what I would like is that the dimension for productmastercode be a calculated one that is the productmastercode & ' - ' & 'one of the productdesc'

similar to the way in TSQL you can do max(productdesc).  I tried max() and maxstring() but both gave a Error in calculation error in the pivot table

Any and all help much appreciated.

Mark

[#2]

you should use a function aggr()  or  firstsortvalue

[#3]

HI Marco

I tried  ....

productmastercode & ' - ' & firstsortedvalue(productdesc, productmastercode)

and this gave the Error in calculation

I also tried ......

aggr(nodistinct productdesc, productmastercode)

this gave results on all lines except the ones where there was a one to many relationship (also tried distinct)

[whiteline]

Use as calculated dimension:

=aggr(productmastercode & ' - ' & productdesc, productmastercode, productdesc)

[#5]

Hi

That is placing a desc beside each line but it is not maintaining the master code piece....

eg

Master Code      Product Code          Product Desc          aggr()

111111               222222                    Test Line 1               111111 - Test Line 1

333333               444444                    Test Line 2               333333 - Test Line 2

333333               555555                    Test Line 3               333333 - Test Line 3

I would like the aggr dimension to in the above display 333333 - Test Line 2 for both lines so that then in the pivot table setup it would allow for a group view of the two product that i can then plus out to.  the aggr as suggested by whiteline shows the mastercode with a description but a new line for each desc so no chance to group on the mastercode.

hope that makes sense

[whiteline · Accepted solution]

=aggr(productmastercode & ' - ' & firstsortedvalue(productdesc, fieldindex('productdesc', productdesc), productmastercode)

fieldindex('productdesc', productdesc) - gives the load order sorting for productdesc.