Set Analysis - Sum Sales if certain conditions are met

[Anonymous]

Name	Role	Opportunity ID
John	A Lead	001
John	A Support	001
Ron	B Rep	002
Ron	B Lead	002
Teressa	Manager	003
John	Manager	001
Teressa	C Lead	003

Opportunity ID	Opportunity Name	Sales
001	Apple	100
002	Orange	200
003	Pineapple	300

Requirement: Sum(Sales) if a person has any sort of Lead or Manager roles (avoiding double counting of sale)

In the above example, John should have a sales amount of 100$ for the opportunity "Apple", because he is a Lead & Manager for that opportunity. But because he has both roles, he should NOT BE DOUBLE COUNTED. same goes for Teressa, her Sales$ should be 300 ONLY.

How do we specify multiple values to match from in a set analysis ?

Something like this: (I know it is syntactically incorrect, but you get the idea what I am looking for)

Sum({<if(Role = Match(Role, 'A lead', 'B Lead', 'Manager'>} Sales)

[sunny_talwar]

May be this....

Sum({<Role = {'*Lead*', '*Manager*'}>}Sales)

[Anonymous]

No, that wouldn't solve the problem of double counting, coz the same person falls into multiple categories

[sunny_talwar]

May be this

Sum(Aggr(Only({<Role = {'*Lead*', '*Manager*'}>}Sales), Name, [Opportunity ID]))

[sunny_talwar]

Check the attached

[jayanttibhe]

I think Hard coding in Set Analsis is not needed . There will be more Roles than that of the example...  Keep it plain and simple

Sum(Aggr((Sales), Name, [Opportunity ID]))

[sunny_talwar]

Oh ya? Even if OP wants to get the sum of sales for Lead and Manager, we just sum everything and keep it simple ?

[jayanttibhe]

Hi Sunny - Your solution is fie if he wants to search Strings in the "Role" ... I Can see he has A Lead  ,  B Lead  -- if data is like that then he has to go hard coding way  in Expression

OR create separate field in Datamodel so create the roles which he is interested based on the Select Strings from his "Role" Field.

[#9]

Hello Abhay,

Please just use this simple set analysis: sum({<Role={"*Lead","Manager"}>}Sales)

You may find the qlikview document in attachment for your reference.

Regards,

Amir

[sam_grounds]

It wouldn't duplicate the sales for the the one person, even if they fulfilled two roles, it would duplicate the sales across multiple people, if say for example one person was the lead and one was the manager. I'd suggest using the lead or manager field only (eg. sum({$<Role={'*Lead'}>}Sales)) and reporting sales by manager afterwards as a separate metric, as there are multiple values which will connect to that value.

Or alternatively, display role as a dimension in a chart so you can see the breakdown. As long as you use the option for "Expression total" (which won't duplicate the rows for totals), but you'll end up with a mismatch of the sum of rows and the final total at the end. If there is a precedence to give the sales to the lead that's a different expression.

If you just want a total, to not display by person, then yes sum({$<Role={'*Lead','*Manager'}>}Sales) would work perfectly.