topic Re: Compare and filter values in two columns based on their difference i in QlikView

Compare and filter values in two columns based on their difference i

amigo007 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have been struggling to add a filter or flag for comparing Sum1 and Sum2? I would like to be able to choose which product Ids have Sum2-Sum1 = 0 and which ones not equal to 0. The ideal soultion would be a flag as filter having both 0 and 'not zero', so if I click 0 I will have only rows with Ids 1 and 4 displayed. Any idea? Thanks in advance.

Id	Sum1	Sum2
1	500	500
2	750	400
3	200	90
4	800	800

Re: Compare and filter values in two columns based on their difference i

johnw — Mon, 31 Oct 2016 18:55:43 GMT

If Sum1 and Sum2 are actual fields in your table, not just in a chart, then in script:

if(Sum2=Sum1,0,'Not 0') as [Difference Flag],

Re: Compare and filter values in two columns based on their difference i

johnw — Mon, 31 Oct 2016 19:03:33 GMT

But let's say they aren't fields in your data model, but calculations in a chart. Say your data has an ID, and fields One and Two, and you're showing a chart of ID, sum(One), and sum(Two). You can make a list box with this expression:

aggr(if(sum(Two)-sum(One)=0,0,'Not 0'),ID)

See attached.

Re: Compare and filter values in two columns based on their difference i

amigo007 — Mon, 31 Oct 2016 20:11:01 GMT

Hello John,

Thanks a lot for your time and your help, much appreciated.

This solution seems to be exactly what I need, however, I got an empty List Box. I have this kind of expression:

=aggr(if(Sum(2)-SUM(1)/1000=0,0,'Not 0'),ID)

I got no error message in the expression box. I had to divide SUM(1) by 100 to get the same units as SUM2.

Re: Compare and filter values in two columns based on their difference i

johnw — Mon, 31 Oct 2016 20:25:38 GMT

What's your actual expression? Might be as simple as a syntax error.

Re: Compare and filter values in two columns based on their difference i

amigo007 — Mon, 31 Oct 2016 20:46:02 GMT

You were right Mr. John, I had to replace 'ID' with the new dimension name that I'm using. Now it works!

Thanks a million. Cheers!