topic Comparing and filtering with null in App Development

Comparing and filtering with null

oddgeir — Wed, 29 Nov 2023 20:32:56 GMT

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: 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.

Re: Comparing and filtering with null

marcus_sommer — Thu, 30 Nov 2023 12:11:16 GMT

You may try something like this:

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

Re: Comparing and filtering with null

oddgeir — Thu, 30 Nov 2023 16:21:34 GMT

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.

Re: Comparing and filtering with null

marcus_sommer — Fri, 01 Dec 2023 06:38:46 GMT

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

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