Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
Try this dollar-sign expansion for formulas:
=sum({$<SalesRepPrimary =, SalesRep ={'*$(=max(SalesRepPrimary))*'}>}Sales)
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
Or this:
=Sum({$<SalesRepPrimary =, SalesRep ={$(=Concat(Chr(39) & '*' & SalesRepPrimary &'*' & Chr(39), ', '))}>}Sales)
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
Yeah, well, max on a text field would not work. Instead, maxstring should have been used:
=sum({$<SalesRepPrimary =, SalesRep ={'*$(=maxstring(SalesRepPrimary))*'}>}Sales)