Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

PersonMonthSales ActivitySales Income
John1100£10,000
John2120£20,000
John380£30,000
John490£4,999
John534£34,000
John680£4,566
John780£9,000
Tom178£1,000
Tom267£40,000
Tom387£34,000
Tom498£56,000
Tom587£23,000
Tom656£3,000
Tom745£4,000

Any help woudl be great.

Thanks

Dan

1 Solution

Accepted Solutions

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

11 Replies

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

Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

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

MVP
MVP

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

Not applicable

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

Community Browser