Salemans sum of Products, even though he was not the salesman of the sale

[fer_wenzel]

Hi guys,

I have a doubt and maybe our community can give me some light!

I have a simple table of salesman, product and sales amount, similar to this:

ScriptTable:

Load * inline [

SalesMan, Product, SalesAmount

John,  Car, 100

John, Motorcycle, 50

Mark, Car, 200

Mark, Motorcycle, 40

Mark, Airplane, 400

];

What I need to do:

• sum the sales amount of the products the salesman has sold, even though he was not the salesman of that sale.
• Do not sum the sales amount of products that salesman has not sold
• This all should not be done in script, since users wishes the value to change whatever the filter he makes

this is what I want to return in a simple table:

SalesMan | Total Amount of the products the salesman’s has also sold

John | 390

Mark | 790

Any ideas?

thanks!

[rwunderlich]

I think 

Sum(Aggr(NODISTINCT Sum(SalesAmount), Product)) 

-Rob

[anthonyj]

Thanks @rwunderlich ,

I really wanted to crack this one but was way off track. (Need to brush up on my aggr( ) with NoDistinct). I am constantly amazed at how a short string of code can do so much in Qlik.

Can I add just one piece for consistent interactivity if filtering on the SalesMan column. A set analysis to make sure that when you choose the SalesMan that the aggregated value remains constant.

Sum(Aggr(NODISTINCT Sum( {$<SalesMan=>} SalesAmount), Product))

Thanks

Anthony