Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

sudhakaran_abha
Contributor

Set Analysis - Sum Sales if certain conditions are met

NameRoleOpportunity ID
JohnA Lead001
JohnA Support001
RonB Rep002
RonB Lead002
TeressaManager003
JohnManager001
TeressaC Lead003

Opportunity IDOpportunity NameSales
001Apple100
002Orange200
003Pineapple300

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)

Tags (1)
9 Replies

Re: Set Analysis - Sum Sales if certain conditions are met

May be this....

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

sudhakaran_abha
Contributor

Re: Set Analysis - Sum Sales if certain conditions are met

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

Re: Set Analysis - Sum Sales if certain conditions are met

May be this

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

Re: Set Analysis - Sum Sales if certain conditions are met

Check the attached

Capture.PNG

jayanttibhe
Contributor II

Re: Set Analysis - Sum Sales if certain conditions are met

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]))

Re: Set Analysis - Sum Sales if certain conditions are met

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
Contributor II

Re: Set Analysis - Sum Sales if certain conditions are met

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.

Not applicable

Re: Set Analysis - Sum Sales if certain conditions are met

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
New Contributor III

Re: Set Analysis - Sum Sales if certain conditions are met

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.

Community Browser