Help - Set Expression - Qlik Community

md_qlikview · ‎2016-10-19

Hello All,

I am having a table wherein i have taken a dimension which carries names of sales representative. I am showing some KPI based on the dimension eg. Gross Amt, Net Amt.

In this chart I want to show amounts for the respective Sales Rep based on the products of which he / she is appointed as head.

Eg. Sales Rep Products

John ABC

Alex PQR

Stuart XYZ

I want to bind the set expression for the amounts that i want to show. How will I achieve this ?

Thanks in Advance,

sunny_talwar · ‎2016-10-19

I don't think I understand what you are looking for. Can you elaborate a little?

johnw · ‎2016-10-19

I'm not sure I understand either, as it doesn't sound like you'd need set analysis or anything complicated.

Dimension 1 = Sales Rep
Expression 1 = sum([Gross Amt])
Expression 2 = sum([Net Amt])

But I do at least have a guess why that isn't working for you, based on "products of which he / she is appointed as the head". My guess is that the sales rep for a given sale of a product may NOT be the sales rep appointed as head for that product. So you have data like this:

John,A,10,5
John,P,5,10
Alex,Q,1,1
Stuart,R,2,2

So if you do what I said above, you end up with this incorrect chart:

John 15 15
Alex 1 1
Stuart 2 2

When what you wanted was this:

John 10 5
Alex 7 13

If so, no problem. Just add this to your data model.

[Primary Reps]:
LOAD * INLINE [
Primary Rep,Product
John,A
John,B
John,C
Alex,P
Alex,Q
Alex,R
Stuart,X
Stuart,Y
Stuart,Z
];

And use Primary Rep instead of Sales Rep as your dimension.

rupamjyotidas · ‎2016-10-19

Dimension Sales Person

Equation

Sum({<Product={'*'}>} Amount)

md_qlikview · ‎2016-10-19

Thanks for replying.

I will go more deeper and try my best to explain. Let me know if you still dont get my point.

Actually my scenario is like this -

As I have mentioned, consider my Sales Rep for which i want to show values are John, Alex and Stuart

Now for John, he is head of one business unit say North. And John total amt will be sum of all the products values sold by John. and that products are eg. ABC, PQR, XYZ. Hence for each product if John is getting 1000 then his total amt will be 3000

But I wanted to show amt for John for only that product which is sold through that business unit of which he is head so in this case North and not any other business unit.Hence for John it will show amt as 1000.

Consider, in case of alex, he is head of South, where only PQR is sold. So in his case only values of PQR should show and not all the products.

johnw · ‎2016-10-19

So not quite my guess, but same idea. John has two different roles. One is "Sales Rep". Another is "Head of Business Unit". To me, a simple answer to make these two different fields, as they are recording rather different facts.

If you want to see what John sold, select John the Sales Rep.

If you want to see what John's unit sold, select John the Head of Business Unit.

leni_harishwara · ‎2016-10-20

Can you share some sample data which replicates your scenario? because in the above sample I didn't find a field to differentiate a SalesRep as Head or not.