I have the below calculated field that finds the max approved amount partitioned by these fields. I also calculated the min and the avg so I have the below table.
But I also want to include the agent that sent this amount. So the max agent and the min agent.
I.e. in the below first line I want in the max agent columns to have Agent A that submitted the 3,869 and in the min agent column Agent B that submitted the 1,650 amount.
Hi @pantelis,
I put together a sample in the Arena view, let me know if this is what you’re trying to achieve:
I took your formula and switched it to a MaxOver function (Max Sales in my example) so that I could use it in a second calc. field (Max Country in my example).
The second calc. field I made is an ifelse that returns the name if max sales equals the sales number.
Yes it as very helpful. I managed to get the agents but now I want to show them in one line because I have the below.
“BI” is the max agent while “Li” is the minimu agent. How I can show them in the same line and get rid of the nulls?
also what if I want to add some filters in this? Because the PRE_AGG does not take filters into account.
Because I dont want just the max over these partitions over the all dataset but I want the below but only for the last 10 orders, only for Enterprise and SMB segment and only for customer id 1001 & 1002?
Hi @pantelis,
You won’t be able to include them in the same column, the system would have no way of knowing to display Max and Min alternating every other line, the tables in QS don’t work that way unfortunately.
In regards to your second question about adding filters, why don’t you try using ‘POST_AGG_FILTER’ instead of ‘PRE_AGG’.
Please review the following articles that further explore Level-Aware Calculations as I feel like these would be very helpful towards a couple of your recent community posts: