topic Set Analysis Syntax in QlikView

Set Analysis Syntax

Thu, 02 May 2013 20:20:09 GMT

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

Re: Set Analysis Syntax

jduenyas — Thu, 02 May 2013 20:59:47 GMT

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.

Re: Set Analysis Syntax

Fri, 03 May 2013 12:46:05 GMT

"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?

Re: Set Analysis Syntax

er_mohit — Mon, 06 May 2013 05:47:35 GMT

try this

min({$<CATable.User_ID=P(OMTable.Sales_Rep),CATable.PorH={'Pending'}>}, CATable.OnDate)

Re: Set Analysis Syntax

Tue, 07 May 2013 20:55:02 GMT

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...

Re: Set Analysis Syntax

swuehl — Tue, 07 May 2013 21:42:03 GMT

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.

Re: Set Analysis Syntax

Thu, 09 May 2013 13:20:58 GMT

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.