Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum other rows based on dimension compare

I've got data that looks like this:

AssociateReferredBySalesQtyReferredSalesQty
AliceBob50100
BobCarl6050
CarlNULL7060
AliceNULL80100
BobAlice9050
DanAlice100

In the `ReferredSalesQty` column, I want to display the SUM of SalesQty they have referred.  So,

Alice is responsible for referring Bob and Dan, resulting in 100 units of sales.

Bob referred Alice, 50 units.

Carl referred Bob, 60 units.

Dan referred no one.

Is this possible with Set Analysis?  I have tried

    SUM({1 Associate = {ReferredBy}} SalesQty)

1 Solution

Accepted Solutions
sunny_talwar

May be try this:

Table:

LOAD Associate,

    ReferredBy,

    SalesQty

FROM

[https://community.qlik.com/thread/228645]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD ReferredBy as Associate,

  Sum(SalesQty) as ReferredSalesQty

Resident Table

Group By ReferredBy;


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

May be try this:

Table:

LOAD Associate,

    ReferredBy,

    SalesQty

FROM

[https://community.qlik.com/thread/228645]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD ReferredBy as Associate,

  Sum(SalesQty) as ReferredSalesQty

Resident Table

Group By ReferredBy;


Capture.PNG

Anonymous
Not applicable
Author

This is helpful.  But anyway to do this with builtin Qlik functions?  I'd like to avoid touching our data model.

sunny_talwar

You want to do this on the front end chart you mean?

Anonymous
Not applicable
Author

Yes.  Maybe using Aggr or set analysis.

sunny_talwar

Not sure if I know how to do this. May be someone else can offer an advice here.

Anonymous
Not applicable
Author

Thanks for your answer.  I researched a little more and try as I might, I can't use any clever aggr(), sum-if's etc.... It seems there is no way to escape dimensionality.  A LEFT JOIN is required for the aggregation I'm looking for.