Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis with AGGR and ONLY

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


1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

6 Replies
giakoum
Partner - Master II
Partner - Master II

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

rubenmarin

Hi Kevin, aggr not accepts set analisys, maybe:

only({1} aggr(FirstSortedValue(CurTab.FullTime,-CurTab.FullTime,2),CurTab.Client))

Not applicable
Author

Hi Ruben,

thanks for the suggestion, but that returns the same "error in calculated dimension".

Not applicable
Author

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.

rubenmarin

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)

Not applicable
Author

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)