Button trigger to set select dates in next week

[jameswills]

HI All,

I'm after some help from the experts with an expression syntax please.

I have a date field, 'Next_Review_Date' in format DD/MMM/YYYY. I'd like an easy solution, button if possible, to select all values where the date is greater than today but less than 7 days from now. Really appreciate if someone could help with the syntax and how to apply please.

[mdmukramali]

Hi,

Hi,

Create An Action with Select in Field .

Field :

Next_Review_Date

Search in String:

='>' & Date(Today(),'DD/MM/YYYY') & '<=' & Date(Today()+7,'DD/MM/YYYY')

[jameswills]

Hi Mohammed,

Thanks for the reply unfortunately it hasn't worked as I'd hoped. When using your exact expression nothing happened. I remove the date formatting i.e. ='>' & Date(Today() & '<=' & Date(Today()+7)) which did select dates but it selected 'all' dates not just the ones in the next week?

I also tried ='>' & Date(Today()) & '<=' & Date(Today()+7) but again selected no dates at all.

Any idea's?

[Mark_Little]

Hi James,

His suggestions looks about right, have you looke dat the QVW he has uploaded?

I would make sure your date field match. Add the expressions to a text box and make ure it returns what is expected.

One thing i noticed is your date format is different to the example provide so try

='>' & Date(Today(),'DD/MMM/YYYY') & '<=' & Date(Today()+7,'DD/MMM/YYYY')

[jameswills]

Hi Mark,

Confused.

='>' & Date(Today(),'DD/MMM/YYYY') & '<=' & Date(Today()+7,'DD/MMM/YYYY') works when popped into a text box but doesn't select dates when added to the button? How do I check what the date format is? Coming through as DD/MM/YYYY in the dimension but my default load script sets it to DD/MMM/YYYY, do I need to update the load script Date Set maybe?

[OmarBenSalem]

Yes please do reformat your datefield using combination of date#() and date()

[jameswills]

Sorry Omar, I'm a newbie to the load script currently have:

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

What do I change?

[Mark_Little]

Hi James,

Yer dates can be lots of fun.

I Normally format as i want in the script.

If you are unsure how they in the data,

First add a new row in your load script

STRING('Next_Review_Date) AS Test Date,

Reload the data and see what is it in the field, if we assume it is DD/MMM/YYYY.

Remove the above and replace Next_Review_Date with

DATE(DATE#(Next_Review_Date,'DD/MMM/YYYY'),'DD/MM/YYYY') as 'Next_Review_Date,

Then make sure the format is now showing as DD/MM/YYYY. The the suggestion should work.

Failing that build up the trigger Bit by bit, i.e.

first get it working with

'<=' & Date(Today()+7,'DD/MMM/YYYY')

Then build it up

[OmarBenSalem]

when you put your Next_Review_Date field as filter; how does it appear? Can you show us a value?

[jameswills]

HI Omar,

Next_Review_Date