Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
May be this....
Sum({<Role = {'*Lead*', '*Manager*'}>}Sales)
No, that wouldn't solve the problem of double counting, coz the same person falls into multiple categories
May be this
Sum(Aggr(Only({<Role = {'*Lead*', '*Manager*'}>}Sales), Name, [Opportunity ID]))
Check the attached
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]))
Oh ya? Even if OP wants to get the sum of sales for Lead and Manager, we just sum everything and keep it simple ?
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.
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
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.