Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Comparing Two Fields - Only records that exist in both sets

Hello,


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

Primary SecIDiRisk Average LifeYear
1

4

12/31/14
2412/31/14
1512/31/15
316/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 SecIDiRisk Average Life - 12/31/14iRisk Average Life - 12/31/15DifferenceYear
145-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!

5 Replies
swuehl
MVP
MVP

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])

Anonymous
Not applicable
Author

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.

swuehl
MVP
MVP

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.

Anonymous
Not applicable
Author

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?

swuehl
MVP
MVP

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()

)