topic Re: Set Analysis - Based on multi box / filter result in QlikView

Set Analysis - Based on multi box / filter result

Sun, 17 Jan 2016 23:27:22 GMT

Hi All,

I am trying to do a set analysis based on the result of a multi box / filter result.

I'll explain my data structure / situation:

-I have 3 Sales Rep fields (SalesRepPrimary, SalesRepSecondary, SalesRepThirdly).

-I am also concatenating the 3 Sales Rep fields into 1, Sales Rep field (SalesRepPrimary &' - ' &SalesRepSecondary &' - ' &SalesRepThirdly as SalesRep) in my script. I'm concatenating so I can use the * wildcard character and search across all 3 fields.

-I'm using SalesRepPrimary as my filter / selection box but I want to search the SalesRep (Concatenated field).

Example Data:

Customer	SalesRep	SalesRepPrimary	SalesRepSecondary	SalesRepThirdly
Cust 1	John - Peter - Sarah	John	Peter	Sarah
Cust 2	Sarah - Peter - John	Sarah	Peter	John
Cust 3	Peter - Sarah - John	Peter	Sarah	John
Cust 4	James - John - Peter	James	John	Peter

If I selected Sarah in the SalesRepPrimary / filter box, I should get Cust 1, Cust 2 and Cust 3 from the above example data, but NOT Cust 4.

If I use:

=sum({$<SalesRepPrimary =, SalesRep ={'*Sarah*'}>}Sales) It works I get the data I want.

I now need to change it to use what is selected in the SalesRepPrimary field.

I have tried the below, and can't seem to get it to work..

=sum({$<SalesRepPrimary =, SalesRep ={'*' &SalesRepPrimary &'*'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &SalesRepPrimary &'*')'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &max(SalesRepPrimary) &'*')'}>}Sales)

Any help would be greatly appreciated.

I hope that you understand my explanation.

If you need any more information or have any questions please feel free to ask.

Thank you very much in advance for your help.

Kind Regards

Jordan

Re: Set Analysis - Based on multi box / filter result

Anonymous — Mon, 18 Jan 2016 08:20:27 GMT

Using these combination:

=sum({$<SalesRepPrimary =, SalesRep ={'*' &SalesRepPrimary &'*'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &SalesRepPrimary &'*')'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &max(SalesRepPrimary) &'*')'}>}Sales)

It seems you want Sales for all representives? If yes then try like this only:

=sum({$<SalesRepPrimary=>}Sales)

Re: Set Analysis - Based on multi box / filter result

Mon, 18 Jan 2016 08:21:29 GMT

Try this dollar-sign expansion for formulas:

=sum({$<SalesRepPrimary =, SalesRep ={'*$(=max(SalesRepPrimary))*'}>}Sales)

Re: Set Analysis - Based on multi box / filter result

Anonymous — Mon, 18 Jan 2016 08:23:15 GMT

This is your wildcard character use =sum({$<SalesRepPrimary =, SalesRep ={'*Sarah*'}>}Sales) which includes {*,?} in qlik.

And here:

=sum({$<SalesRepPrimary =, SalesRep ={'*' &SalesRepPrimary &'*'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &SalesRepPrimary &'*')'}>}Sales)

=sum({$<SalesRepPrimary =, SalesRep ={'$('*' &max(SalesRepPrimary) &'*')'}>}Sales)

You are appending it with SalesRepPrimary which returns you *SalesRepPrimary* and it does not exist in your fileld and hence return nothing

Re: Set Analysis - Based on multi box / filter result

sunny_talwar — Mon, 18 Jan 2016 12:07:58 GMT

Or this:

=Sum({$<SalesRepPrimary =, SalesRep ={$(=Concat(Chr(39) & '*' & SalesRepPrimary &'*' & Chr(39), ', '))}>}Sales)

Re: Set Analysis - Based on multi box / filter result

Mon, 18 Jan 2016 20:20:10 GMT

Thanks for your replies.

I have been able to get it to work below is my formula

=sum({$<SalesRepPrimary =, SalesRep ={'$(='*' &trim(SalesRepPrimary) &'*')'}>}Sales)

Thanks for your help

Regards

Jordan

Re: Set Analysis - Based on multi box / filter result

Tue, 19 Jan 2016 07:52:45 GMT

Yeah, well, max on a text field would not work. Instead, maxstring should have been used:

=sum({$<SalesRepPrimary =, SalesRep ={'*$(=maxstring(SalesRepPrimary))*'}>}Sales)