Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table of activities by CSRs for our clients (only important fields shown) (table name is CurTab)
Client CSR ActDate Note
ABC Tom 2/03/15 Client wants sales rep to call
DEF Harry 2/03/15 Sent renewal notice
ABC Dick 2/02/15 Added new user and gave login ID
- etc -
Management wants a report on activity for ONLY the previous day (e.g. today is 2/04/15 (we use US style dates)), However, they also want to see the latest PREVIOUS call to the company. When I first built the table, I just used formula such as:
CurrentCall= Firstsortedvalue(CurTab.ActDate,-CurTab.ActDate,1) and
PreviousCall= Firstsortedvalue(CurTab.ActDate,-CurTab.ActDate,2)
but as experienced users will note, that gave me the latest date for all the clients, not the latest date for a particular client. So, I used
AGGR:
CurrentCall = aggr(Firstsortedvalue(CurTab.ActDate,-CurTab.ActDate,1),CurTab.Client)
PreviousCall = aggr(Firstsortedvalue(CurTab.ActDate,-CurTab.ActDate,2,CurTab.Client)
and all was good. However, we also use nPrinting to create and distribute reports, and when I set the filter for the report to "ActDate=today(0)-1", I soon found out that selecting 2/03/15 as ActDate meant that no other dates were available, and PreviousCall returned Null. I was having issues with Set Analysis at the time (as in, I couldn't figure it out!), so I kluged it by creating a second table, PastTab, with all the same fields as CurTab, and substituting PastTab for CurTab in the PrevousCall expression. That worked, but it seems heavy handed. Now that I've taken a course on SA (and at least understand the syntax), I'd like to do this more elegantly. So I tried the following:
PreviousCall = only(aggr({1} FirstSortedValue(CurTab.FullTime,-CurTab.FullTime,2),CurTab.Client))
To my mind, this should: aggregate the entire set of CurTab - regardless of user or nPrinting selection - by Client, and find the date of the previous call. I used the ONLY function because my understanding is AGGR needs to be wrapped by another aggregation function.
This returns "error in calculated dimension". I took out the ONLY function; same result. I tried moving it from a dimension to an expression, but the expression returns NULL. Please note I have checked my underlying data, and using my old two-table technique, these fields do populate correctly, so the data is there.
I would like to do this more elegantly, as I am sure this will crop up again in the future. Can anyone see what I'm doing incorrectly?
thanks, Kevin
I answer myself!
The solution is to put the Set Analysis inside FIRSTSORTEDVALUE. For some reason, I thought it had to be in the outer function, but that is not so.
So, the answer is:
aggr(FirstSortedValue( {1} CurTah.Fulltime,-CurTab.FullTime,2),CurTab.Client)
if you are using this as calculated dimension, then maybe replacing only with valuelist would help.
A sample application would be helpful if you could
Hi Kevin, aggr not accepts set analisys, maybe:
only({1} aggr(FirstSortedValue(CurTab.FullTime,-CurTab.FullTime,2),CurTab.Client))
Hi Ruben,
thanks for the suggestion, but that returns the same "error in calculated dimension".
Thanks for the suggestion, but I don't know in advance when the previous call was - it could have been (and has been) more than six months ago. The valuelist would be quite long in that case.
Yep, that suggestion was a guessing, my main point is that aggr can't take set analisys itself, as Ionnais saids, one sample will be nice.
To give another guess:
aggr(only({1} FirstSortedValue(CurTab.FullTime,-CurTab.FullTime,2)),CurTab.Client)
I answer myself!
The solution is to put the Set Analysis inside FIRSTSORTEDVALUE. For some reason, I thought it had to be in the outer function, but that is not so.
So, the answer is:
aggr(FirstSortedValue( {1} CurTah.Fulltime,-CurTab.FullTime,2),CurTab.Client)