Help: Criteria? Set Analysis? Across records

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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

[Miguel_Angel_Baeyens]

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

[#5]

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

[#6]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#8]

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

[swuehl]

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

[#10]

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