04-24-2013 04:48 AM
Assume the following table with 3 fields:
Contract Start_Dte End_Dte
CTR001 01JAN2013 31DEC2015
CTR002 01JUL2012 31DEC2014
CTR003 01JUL2012 31JAN2013
I want to build an InfoMap allowing the user to compare the number of active contracts on 2 different dates, for instance on 31DEC2012 there are 2 active contracts (CTR002 and CTR003) and on 31MAR2013 there are also 2 active contracts (CTR001 and CTR002).
To achieve this I want to build an InfoMap with a Data Item with a dynamic name, being the result of
CASE WHEN "&date"d between Start_Dte and End_Dte then 1 else 0 as Dte_&date
where &date is the result of a prompt. So the name of the data item depends on the answer on a prompt.
In a query the user should be able to select this data item twice, generating 2 columns in the result, for instance: Dte_31DEC2012 and Dte_31MAR2013.
Is this possible?