Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Pivot table to show all row values despite the selection

Hi, I have two tables which have a connection by "userID".

Table 1. userID, Industry (possible values X & Y)

Table 2. userID, sales, item (possible values: Item1 & Item2

I made a pivot table where I want to see total sales of item1 for all users in industry x.

The problem is that I want to see all userid:s that I have chosen from Table 1, no matter which Sales I choose (item1 or item2).

What happens in pivot is that If Table 1:s userid doesnt have any sales on item1, then he is not in the final list. So id need pivot to show null values for those users that dont have sales in item 1 but who are in Table 1 selection just like in excel.

I need to do this in the app so the solution is not to do joins in data load phase. 

 

Thanks for any help provided.

2 Replies
Highlighted
Partner
Partner

Try the expression below.
It is not affected by selection in the item field.
Sum({<item=>sales)

Highlighted
Contributor
Contributor

Hi, where do I put this? It cant be in the aggreation part (measures->expression) as then I cant filter total sales by item. 

The idea is that Id like get certain item sales for certain industries. 

 

Table 1. userID, Industry (possible values X & Y)

Table 2. userID, sales, item (possible values: Item1 & Item2

 

For example, I want a list from all usersID:s in Table 1 for industry X and their sales for Item1 (no matter did they have sales for Item1 or not). 

Should I put some "do not include this variable" in part where I have UserId currently (as row of pivot table)?