Using Prompt Manger To Subset Data With Date Values

[JDMTX]

I have created a dynamic prompt in my project that allows the user to select a date (ex:  15Dec2012) and then this prompt to to subset the data via the use of a query filter.  Is there away to create a second prompt that calculates back exactly one month from the user selected value in the first prompt so that I can create a second data subset that containts only the data for the prior month (ex:  15Nov2012)?

Any help is deeply appreciated.

Many Thanks,

James

[TomKari]

Hi, James

Is there any reason for you to prompt the user? Or do you simply want to calculate the date one month earlier, to use as a lower bound?

Tom

[JDMTX]

Tom,

In this particular usage case, the user of the project needs to be able to select the month that they are interested in and then using this informatin calculate back one month so that we can produce a report that segments the data by current analysis period and the prior month on a dynamic basis.

Regards,

James

[Astounding]

James,

The basic tool for the job is the INTNX function, something along the lines of:

begin_date = intnx('month', "&userdate"d, -1);

Look at the 4th parameter to INTNX as well, which lets  you select SAME to get the same day of the month.  Think about what you want to happen if the user specifies a date designating an uncertain time period, such as February 28th, or March 30th.  The way that INTNX handles this with SAME may or may not correspond to the time period you would like to select.

Good luck.

[JDMTX]

Astounding,

Your approach worked perfectly.  Thanks for the help!!!

James