Qlik Community

Qlik Sense App Development

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

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
crissaegrim
New Contributor III

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

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

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
New Contributor III

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.

Re: Sum other rows based on dimension compare

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

crissaegrim
New Contributor III

Re: Sum other rows based on dimension compare

Yes.  Maybe using Aggr or set analysis.

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
New Contributor III

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.