Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Federico_Rizzello

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

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:

LabelCustomerIDCustomer Sales Customer Linked Sales
CommentThe final buyerTotal 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 resultACME SRL1.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

1 Solution

Accepted Solutions
jerifortune
Creator III
Creator III

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.

View solution in original post

6 Replies
jerifortune
Creator III
Creator III

No sure if I get you right.

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

Federico_Rizzello
Author

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
Creator III
Creator III

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

Federico_Rizzello
Author

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
Creator III
Creator III

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.

View solution in original post

Federico_Rizzello
Author

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