Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables from our contact management system, CATable (Calendar), and OMTable (Opportunity).
I want to select the first pending activity after today that has been entered by the Sales Rep who is assigned to the account.
I tried:
min({<CATable.User_ID=OMTable.Sales_Rep,CATable.PorH={'Pending'}>}, CATable.OnDate)
but even though it said "Expression OK", I don't get any results. When I break it down into two expressions (i.e. two columns):
min({<CATable.PorH={'Pending'}>}, CATable.OnDate) works just fine, but
min({<CATable.User_ID=OMTable.Sales_Rep>} CATable.OnDate) I just get nulls.
I thought I was following the sample syntax scrupulously. Can I not compare two items from two different tables?
Thanks,
Kevin
The expression CATable.User_ID=OMTable.Sales_Rep in the set analysis is incorrect.
Should be CATable.User_ID={<some value>}
You state that min({<CATable.PorH={'Pending'}>}, CATable.OnDate) works just fine
as it should be.
"The expression CATable.User_ID=OMTable.Sales_Rep in the set analysis is incorrect.
Should be CATable.User_ID={<some value>}"
er, the example given in the text is <OrderDate=DeliveryDate>, which certainly looks to me like it's two fields being compared to each other. However, those fields might be from the same table. So my question remains: can you compare two fields from different tables to each other in set analysis?
try this
min({$<CATable.User_ID=P(OMTable.Sales_Rep),CATable.PorH={'Pending'}>}, CATable.OnDate)
Thanks, mohit, but I don't see how that helps; I don't want to restrict the choice to ANY Sales Rep. I want it restricted to the Sales_Rep who is assigned to the account.
Surely there has to be a simple way to get the answer to this question:
What was the last result when the assigned Sale Rep contacted the customer?
I've tried any number of things:
if(CA.User_ID=OM.Sales_Rep,firstsortedvalue(CA.ResultCode,-CA.FullTime))
firstsortedvalue(${<CA.User_ID={OM.Sales_Rep{>} CA.ResultCode,-CA.FullTime)
etc.
but nothing works! Please, there must be an answer somewhere...
First, I believe
=min({<CATable.User_ID=OMTable.Sales_Rep,CATable.PorH={'Pending'}>}, CATable.OnDate)
is valid syntax. If you check the Help, you'll find that you can either use an element list, an element function or a field name to the right of the equal sign in a set modifier field selection.
I would avoid to call this a comparison. I think it's better called a value assignment in a field selection.
To help you with your expression, I am missing some information on your data model and the context you are using the expression in.
Could you post some more details?
I am just assuming you are using the set expression in a table chart, maybe with Account as dimension.
In general, set analysis is evaluated per chart, not per dimension value, so Sales_Rep probably won't show an unambiguous value when evaluated.
Hi Stefan,
"In general, set analysis is evaluated per chart, not per dimension value, so Sales_Rep probably won't show an unambiguous value when evaluated."
Well, that might explain the situation - I was looking for something that was evaluated per dimension of "Company". I tried using "aggr" in a variety of ways without success, so I thought maybe set analysis would help, but based on what you said above, it's not going to work.
I would have thought that:
=aggr(min(if(CATable.User_ID=OMTable.Sales_Rep AND CATable.PorH='Pending, CATable.OnDate)),Company) would do the job, but it doesn't work either.