Presentation variables in the between prompt

Question

Hi Gurus,

I have a prompt  which values between two dates as shown in the attachment. Is there any way to just capture only one date in the presentation variable ?

For ex : User chooses 04/01/2018 to 04/24/2018. I just want to capture 04/24/2018 and use it to populate calculated field.

Thnaks

Venkat

Adam Wickes · Answer

Something similar to the following will also work (where p_test = the name of your variable).

SUBSTRING('@{p_test}{01/01/1900}' FROM LOCATE(',', '@{p_test}{01/01/1900}')+1 FOR LENGTH('@{p_test}{01/01/1900}')-LOCATE(',', '@{p_test}{01/01/1900}'))

Martin's method is by far the cleanest though.

Robert Angel · Answer

Your other option is to create two dashboard prompts one for the from, the other for the two, populating presentation variables and then filtering based on these with two separate filters, or one sql based file, in your analysis.

Martin van Donselaar · Answer

The presentation variable that the prompt creates will store the dates like 04-01-2018,04-24-2018  (dates are separated by column)

You can get the second date like this: SUBSTRING('@{pVar}' FROM POSITION(',' IN '@{pVar}')+1 )

Date format in the presentation variable string can vary based on your NLS settings