Set Analysis - Based on multi box / filter result

[#1]

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

[Anonymous]

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)

[#3]

Try this dollar-sign expansion for formulas:

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

[Anonymous]

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

[sunny_talwar]

Or this:

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

[#6 · Accepted solution]

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

[#7]

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

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