topic Comparing Two Fields - Only records that exist in both sets in QlikView

Comparing Two Fields - Only records that exist in both sets

Anonymous — Tue, 12 Jan 2016 20:45:46 GMT

Hello,

I have a table with key fields that are as follows:

Primary SecID	iRisk Average Life	Year
1	4	12/31/14
2	4	12/31/14
1	5	12/31/15
3	1	6/30/15

I have used a calendar object to create two date variables (vStartDate and vEndDate).

I want to compare a record (e.g. [iRisk Average Life]) across the two dates selected but only display the comparison if there is a match in the unique identifying field, which is [Primary SecID] and there is a difference between the two field in the two dates.

That is I want to only show those records where the 'Primary SecID' has changed from Date 1 to Date 2.

In this instance, if I selected 12/31/14 as date 1 and 12/31/15 as date 2, the results would be as folllows:

Primary SecID	iRisk Average Life - 12/31/14	iRisk Average Life - 12/31/15	Difference	Year
1	4	5	-1

I am thinking the formula should be something like this...

sum({$<A_ASOFDATE={vStartDate},[iRisk Average Life] = p({$<A_ASOFDATE={vEndDate}>}[Primary SecID)>}IDATE)

Please help, thank you!

Re: Comparing Two Fields - Only records that exist in both sets

swuehl — Wed, 13 Jan 2016 01:56:55 GMT

Maybe like attached (two alternatives shown), e.g. second alternative shows straight table with dimension

Primary SecID and three expressions:

=if(Column(3),Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]))

=if(Column(3),Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life]))

=Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life])

Re: Comparing Two Fields - Only records that exist in both sets

Anonymous — Wed, 13 Jan 2016 17:05:46 GMT

Thank you! In this example. What is "Column(3)" referencing to?

I am trying to build this in my dashboard but it is not working.

Re: Comparing Two Fields - Only records that exist in both sets

swuehl — Wed, 13 Jan 2016 17:29:05 GMT

Column(3) is referencing to the third expression column

=Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life])

If all three expressions return zero or NULL and if 'suppress zero values' is enabled on presentation tab, only Primary SecID with a difference between years in field iRisk Average Life will be shown.

Re: Comparing Two Fields - Only records that exist in both sets

Anonymous — Wed, 13 Jan 2016 19:04:16 GMT

Hey!

Thank you so much! It is working, however it is still showing results where the value exists for one date and not the other even though "Supress null values" is selected.

Any ideas?

Re: Comparing Two Fields - Only records that exist in both sets

swuehl — Wed, 13 Jan 2016 19:26:43 GMT

Maybe add a condition:

If( Count({<Year = {'$(vStartDate)','$(vEndDate)'} >} DISTINCT [iRisk Average Life]) =2,

Only({<Year = {'$(vStartDate)'}>} [iRisk Average Life]) - Only({<Year = {'$(vEndDate)'}>} [iRisk Average Life]),

NULL()

)