Skip to main content
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Comparing and filtering with null


I am trying to set up comparison of two selected snapshots to find the difference. 
However I can't find how to keep unmatched data. Is there a good way to do this?

Here's some simplified data



Load * Inline [
Day	, Item 		, Qty
1	, Apples	, 2
1	, Pears		, 4
1	, Plums		, 5
2	, Apples	, 1
2	, Pears		, 3
2   , Bananas	, 7
3	, Apples	, 4
3	, Plums		, 2
3   , Bananas	, 4



I want to give the user an option to select two days, and have the diff calculated automatically. However the unmatched data give me a headache. I can't get this set up (in a way that scales) without missing information when there's no match. 

I want a table with the Items where I have one column with sum for the selected first day, and another column with the selected second day. Like this

  1 3 Diff
Apples 2 4 2
Pears 4 0 -4
Plums 5 2 -3
Bananas 0 4 4

(Day selection doesn't have to be header. That's just for the convenience of display)

As this table will have a significant amount of data it should be possible to still search/filter the table just like a normal table.

Labels (1)
3 Replies

You may try something like this:

rangesum(-sum({< Day = {1}>} Qty), sum({< Day = {3}>} Qty))

Contributor III
Contributor III

Yes Set Analysis can work. Only issue with this solution is that  "I want to give the user an option to select". I guess I'll have to get the values from some selection into some variables then.


In this case you will need a calculated dimension, maybe with something like:

aggr(rangesum(-sum({< Day = {1}>} Qty), sum({< Day = {3}>} Qty)), Item)