Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Comparing and filtering with null

Hi

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

``````Data:
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)
• ### General Question

3 Replies

You may try something like this:

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

Contributor III
Author

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)