Concatenate line expresion to header expression

[#1]

Hi all,

We have an "allocation" expression with set analysis that produces a concatenate field (per sales quote line) of all the salesperson and the allocation % for that line. As a new request, I need to produce a "header Sales Quote" report that also contains that allocation, however when I use the existing expression, if the order has three lines the Allocation expression provides the allocation x3 instead of an average per sales person.

Allocation Expression:

concat({$<Transaction={'Quote'}, Ledger={'Actuals'}, [Sales Order Status]={'Open'},[Super Category]={'Software'},[Allocation Line]={">0"}>}[Salesperson] & '@' & num([Allocation], '0%'), ', ')

[Salesperson] and [Allocation] are both calculated per line.

Would anyone be kind enough to point at possible solutions for this?

Thanks a million in advance,

Daniel

[Gysbert_Wassenaar]

Maybe you need to calculate the average first before concatening:

concat({$<Transaction={'Quote'}, Ledger={'Actuals'}, [Sales Order Status]={'Open'},[Super Category]={'Software'},[Allocation Line]={">0"}>} distinct [Salesperson] & '@' & num(aggr(avg({$<Transaction={'Quote'}, Ledger={'Actuals'}, [Sales Order Status]={'Open'},[Super Category]={'Software'},[Allocation Line]={">0"}>}[Allocation]),[Salesperson]), '0%'), ', ')

[#3]

Hi Gysbert,

It was closer but not quite yet. For an example where Chris Harnett gets a 0% and Max Morton a 100% on each line, this is the result I get with your expression (this quote has 5 lines)

Chris Hannett@, Chris Hannett@, Chris Hannett@, Chris Hannett@, Chris Hannett@0%, Max Morton@, Max Morton@, Max Morton@, Max Morton@, Max Morton@100%

Seems like two of the aggregate formula works (only two have the correct 0% and 100%), however the names are still coming up as many times as lines the quote has.

Would there be a formula that could make it limit this?

thanks a lot!

[Gysbert_Wassenaar]

concat( distinct ...stuff...)