Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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;
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;
This is helpful. But anyway to do this with builtin Qlik functions? I'd like to avoid touching our data model.
You want to do this on the front end chart you mean?
Yes. Maybe using Aggr or set analysis.
Not sure if I know how to do this. May be someone else can offer an advice here.
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.