Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
crissaegrim
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)

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Sum other rows based on dimension compare

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

6 Replies
sunny_talwar
Not applicable

Re: Sum other rows based on dimension compare

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

crissaegrim
Not applicable

Re: Sum other rows based on dimension compare

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

sunny_talwar
Not applicable

Re: Sum other rows based on dimension compare

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

crissaegrim
Not applicable

Re: Sum other rows based on dimension compare

Yes.  Maybe using Aggr or set analysis.

sunny_talwar
Not applicable

Re: Sum other rows based on dimension compare

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

crissaegrim
Not applicable

Re: Sum other rows based on dimension compare

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.