Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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%'), ', ')
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!
concat( distinct ...stuff...)