Expression in table/pivot table to show totals over same field

[Federico_Rizzello]

Hi to all,

I have a data model with this table:

SALES

DocumentID

Linked_CustomerID

CustomerID

Sales_Value

The field "Linked_CustomerID" is a referral to a "CustomerID": each document has a "CustomerID" (final buyer), but sometimes these customers buy using a referral link given to them by another customer (inviting customer), stored in the field "Linked_CustomerID".

My objective is creating a straight/pivot table with this structure:

Label	CustomerID	Customer Sales	Customer Linked Sales
Comment	The final buyer	Total direct sales of the customer (sum(Sales_Value))	Total sales of all the other customers who used the referral link of the customer "CustomerID"
Expected result	ACME SRL	1.000 €	250 €

My problem is: what is the correct expression and set analysis for the column "Customer Linked Sales" in order to achieve the above table?

I tried different expressions, using aggr() and p() but without success. The thing is that my users should get a table by "CustomerID" with 1) the total direct sales of that specific customer and 2) the total sales of all the other customers invited by him. Is this even possibile?

Thanks in advance for any advice. Best regards.

- Federico Rizzello

[jerifortune]

No sure if I get you right.

You want to get the total of 1250 against customer ACME SRL?

[Federico_Rizzello]

Hi Jerifortune, thanks for your interest.

No, I don't need to calculate the total of 1.250 €, I'm in search of the formula to calculate the column "Customer Linked Sales" of 250 € against each customer. The idea behind it is:

calculate the sum of Sales of other customers who purchased using the referral link of the customer ACME

[jerifortune]

Do you have a sample data? I think it is more on the modeling side than expression.

[Federico_Rizzello]

Sorry for the late reply, and thanks for your interest.

I attached some raw data and an app with sample data and some comments about my problem. Thanks for any advice!

[jerifortune · Accepted solution]

Hi Federico,

Sorry, I was busy with other projects and didn't come here since your last response.

I adjusted your model as :

Set dataManagerTables = '','Sheet1';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;

Unqualify *;

[Sheet1]:
LOAD
[Document_ID],
[CustomerID],
[Sales_Value],
[Linked_CustomerID]
FROM [lib://Desktop/QlikCommunityExample.xlsx]
(ooxml, embedded labels, table is Sheet1);

CustomerSales:
LOAD //[Document_ID],
[CustomerID],
[Sales_Value]

Resident [Sheet1]
WHERE LEN([Linked_CustomerID]) < 1
;
NoConcatenate
LinkedCustomerSales:
LOAD
// [Document_ID],
[Linked_CustomerID] AS [CustomerID],
[Sales_Value] AS LinkedCustomerSales,
[Linked_CustomerID]
Resident [Sheet1]
WHERE LEN([Linked_CustomerID]) > 0
;

Hope this helps.

[Federico_Rizzello]

Sorry for the late answer, I had to find time for testing. Your solution was correct: the problem was in the model. Using a different model solved the problem.

I modified your solution to better suit my idea, creating a concatenated table:

//Read raw data
[Tmp_Sales]:
LOAD
[Document_ID],
[CustomerID],
[Sales_Value],
[Linked_CustomerID]
FROM [lib://Desktop/QlikCommunityExample.xlsx]
(ooxml, embedded labels, table is Sheet1);



//Direct sales table
[Sales]:
LOAD 
[Document_ID]&'|'&[CustomerID] as [_Key_Sales],
[CustomerID],
[CustomerID] as [FinalCustomerID],
[Document_ID],
[Sales_Value],
'Direct' as [Sales Type]
Resident [Tmp_Sales];

Concatenate

//Linked sales
[Linked_Sales]:
Load
[Document_ID]&'|'&[Linked_CustomerID] as [_Key_Sales],
[Linked_CustomerID] as [CustomerID],
[CustomerID] as [FinalCustomerID],
[Document_ID],
[Sales_Value] as [Linked_Sales_Value],
'Linked' as [Sales Type]
Resident [Tmp_Sales]
Where IsNull([Linked_CustomerID])=False(); //this condition includes only linked sales, identified by the presence of a linked_CustomerID; if null, then it's a direct sale 



//Drop raw data
drop table [Tmp_Sales];

The final solution is attached to this post. Thanks a lot for your help and your time, Jerifortune. Best wishes.

- FR