Sales Amount - Set Analysis

[qlikviewaf]

Hi Guys,

i'm building an easy report by Sales Rep.

Basically each sales transaction is associated to a sales Rep.

In the report i would like to have this structure as shown below: Sales Rep -> Customer

As expression i would like to see the sales amount of that specific rep, but also the total sales amount (regardless which was the sales-rap) for that specific customer

In the example below in the Total Customer sales amount i expect to see on each row 600 which is the total sales by customer.

I try to set this formula but it doesnt work --> =Sum({<SalesRep=,}>} Amount)

Can anyone please help?

Thank you!!

[sunny_talwar · Accepted solution]

I think you need What does the TOTAL qualifier do? instead of set analysis

=Sum(TOTAL <Customer> Amount)

[qlikviewaf]

Hi Sunny,

this is working -  but if a specific Sales Rep is selected, this will not work anymore.

Which solution can be used in this case?

[devarasu07]

Hi,

just ignore this sales rep dimension form your expression

like this

=Sum(TOTAL <Customer> {<SalesRep=>}Amount)

[qlikviewaf]

Hi Devarasu,

I selected NickRep, but...i would like not to have the TonyRep row then. but just customer linked to NickRep.

Do you think this is something possible?

[devarasu07]

Hi,

Yes,

=Sum(TOTAL {<SalesRep=>}  Amount)

[qlikviewaf]

Is not working, is summing all sales regardless of customer

[devarasu07]

Hi,

Can you try like this,

SalesData:
LOAD * INLINE [
InvoiceDate,Customer, SalesRep, Amount
01/01/2017, ACOUSTIC LTD, NickRep, 100
05/01/2017, ACOUSTIC LTD, TonyRep, 500
06/01/2017,ARMONIC SDC, Giorione, 230
];
[Total Sales]:
load Customer,sum(Amount) as [Total Sales] Resident SalesData group by Customer;

[sunny_talwar]

Like devarasu07‌ mentioned, you needed this

=Sum(TOTAL <Customer> {<SalesRep=>}Amount)