11 Replies Latest reply: Aug 6, 2012 5:38 AM by Daniel Garguilo

# Help: Criteria? Set Analysis? Across records

Hi All,

Is there any way to set up a filter criteria that would go across all records for a person? IFor example, in the table below is it possible to select the sales person where in any Month Sales Activity was >100 and also in any month Sales Income was <£10,000. John would meet this criteria as in Months 1 and 2 Activty was > 100 and in months 4, 6 and 7 Sales income was <£10,000. Tom would not meet the criteria as at no point was Sales activity >100.

 Person Month Sales Activity Sales Income John 1 100 £10,000 John 2 120 £20,000 John 3 80 £30,000 John 4 90 £4,999 John 5 34 £34,000 John 6 80 £4,566 John 7 80 £9,000 Tom 1 78 £1,000 Tom 2 67 £40,000 Tom 3 87 £34,000 Tom 4 98 £56,000 Tom 5 87 £23,000 Tom 6 56 £3,000 Tom 7 45 £4,000

Any help woudl be great.

Thanks

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Dan,

This is possible, indeed. Assuming all the columns above are fields (not expressions) the final expression should look like the following:

```Count({< [Sales Activity] = {'>100'}, [Sales Income] = {'<=10000'} >} Person)
```

Assuming "Sales Activity" and "Sales Income" are expressions, the final expresion would be very similar,  looking like this:

```Count({< Person = {"=Sum([Sales Activity]) > 100", "=Sum([Sales Income]) <=10000"} >} Person)
```

Hope that helps.

Miguel

• ###### Re: Help: Criteria? Set Analysis? Across records

HI Miguel,

Thank you for the quick reply.

I think the experesion you propose only checks to see if the criteria is meet on one row. Is there anyway to check to see if both parts are meet on any row for that sales person? I have attached a quick example to show that the are no sales people that meet both criteria for one row however John does meet both individual Criteria. I am keen to do this in one experssion. I have been thinking maybe i need to use 'aggr' in combination with min/max however feel like there must be a simplier way.

Thanks again,

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Dan,

I'd use two sets of modifiers instead of one, then. Check the attached application for both conditions (the first AND the second or the first OR the second).

Hope that makes more sense now.

Miguel

• ###### Re: Help: Criteria? Set Analysis? Across records

HI Miguel,

Thanks again. The example is still not flagging John as meeting both criteria at any point in time. The 'And' column is close to what i require however the 'And' needs to Ignore the month and check to see if it meets Criteria 1 and Criteria 2 regardless of month. Sorry if  I am not explaining this very well. Does that make sense?

Thanks

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

HI Miguel,

Thanks again. The example is still not flagging John as meeting both criteria at any point in time. The 'And' column is close to what i require however the 'And' needs to Ignore the month and check to see if it meets Criteria 1 and Criteria 2 regardless of month. Sorry if  I am not explaining this very well. Does that make sense?

Thanks

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Dan,

Test the attached. The idea is very similar, but using two Aggr() functions. It would be quite faster if the amount of records is high to do this in the script.

Miguel

• ###### Re: Help: Criteria? Set Analysis? Across records

HI Miguel,

That does the trick. If you add a nodistinct in the AGGR then this also updates teh value for all rows for the person. See attached.

Ideally, i would like this to be a very flexible functionality that end users could use easily. The may want to do this for any number of fields and potentially for any number of values. They will also need to be able to choose <, > or =.  Can you think of any way that i could set this up?  I am happy to create new feilds as required. They could do it using advanced selection however this is not ideal for them.

I am thinking maybe i could add a min and max field for every field which would allow users to filter min/max values using list boxes however this would not allow them to use the '=' functioanality.

Thanks

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Another idea using only a single set expression to filter the Persons:

{<Person = p({<[Sales Activity] = {">100"}>}) * p({<[Sales Income]={"<10000"}>}) >}

For example, in a list box field expression use:

=aggr(only({<Person = p({<[Sales Activity] = {">100"}>})*p({<[Sales Income]={"<=10000"}>})>}Person),Person)

to get only John as possible value.

If you want to offer your users a more flexible functionality, you can try to create the field modifier for Person dynamically (i.e. the p(...)*p(...) part), using string functions. But that's probably quite a lot of work.

Regards,

Stefan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Miguel and Stefan,

Thank you for your very helpful responses. I look forward to working through these further next week and putting them into practice.

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Miguel and Stefan,

Thank you for your very helpful responses. I look forward to working through these further next week and putting them into practice.

Dan

• ###### Re: Help: Criteria? Set Analysis? Across records

Hi Miguel and Stefan,

I have just posted another set analysis query on the community. I am trying to compare dates across two fields and multiple records and having a little (well a lot) trouble. If you could spare some time to help out that would be great as I have already spent quite a few hours on this and got no where

http://community.qlik.com/message/245416#245416

Kind regards,

Dan