topic Re: Sales Amount - Set Analysis in QlikView

Sales Amount - Set Analysis

qlikviewaf — Wed, 22 Nov 2017 17:08:14 GMT

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!!

Re: Sales Amount - Set Analysis

sunny_talwar — Wed, 22 Nov 2017 17:11:43 GMT

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

=Sum(TOTAL <Customer> Amount)

Re: Sales Amount - Set Analysis

qlikviewaf — Wed, 22 Nov 2017 21:27:46 GMT

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?

Re: Sales Amount - Set Analysis

devarasu07 — Thu, 23 Nov 2017 01:47:13 GMT

Hi,

just ignore this sales rep dimension form your expression

like this

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

Re: Sales Amount - Set Analysis

qlikviewaf — Thu, 23 Nov 2017 08:22:10 GMT

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?

Re: Sales Amount - Set Analysis

devarasu07 — Thu, 23 Nov 2017 08:29:31 GMT

Hi,

Yes,

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

Re: Sales Amount - Set Analysis

qlikviewaf — Thu, 23 Nov 2017 08:48:30 GMT

Is not working, is summing all sales regardless of customer

Re: Sales Amount - Set Analysis

devarasu07 — Thu, 23 Nov 2017 09:10:33 GMT

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;

Re: Sales Amount - Set Analysis

sunny_talwar — Thu, 23 Nov 2017 23:12:58 GMT

Like devarasu07‌ mentioned, you needed this

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