topic Re: Sum other rows based on dimension compare in App Development

Sum other rows based on dimension compare

Anonymous — Wed, 10 Aug 2016 16:26:46 GMT

I've got data that looks like this:

Associate	ReferredBy	SalesQty	ReferredSalesQty
Alice	Bob	50	100
Bob	Carl	60	50
Carl	NULL	70	60
Alice	NULL	80	100
Bob	Alice	90	50
Dan	Alice	10	0

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)

Re: Sum other rows based on dimension compare

sunny_talwar — Wed, 10 Aug 2016 16:56:51 GMT

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;

Re: Sum other rows based on dimension compare

Anonymous — Wed, 10 Aug 2016 16:58:44 GMT

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

sunny_talwar — Wed, 10 Aug 2016 17:00:09 GMT

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

Re: Sum other rows based on dimension compare

Anonymous — Wed, 10 Aug 2016 17:20:08 GMT

Yes. Maybe using Aggr or set analysis.

Re: Sum other rows based on dimension compare

sunny_talwar — Wed, 10 Aug 2016 17:26:24 GMT

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

Re: Sum other rows based on dimension compare

Anonymous — Fri, 12 Aug 2016 20:10:18 GMT

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.