QlikView App Dev

[gregoryt40]

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!

[swuehl]

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

[gregoryt40]

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]

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.

[gregoryt40]

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]

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

)