topic Re: Button trigger to set select dates in next week in QlikView

Button trigger to set select dates in next week

jameswills — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Button trigger to set select dates in next week

mdmukramali — Fri, 17 Nov 2017 16:55:28 GMT

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

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 14:47:35 GMT

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?

Re: Button trigger to set select dates in next week

Mark_Little — Wed, 22 Nov 2017 14:52:22 GMT

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

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 15:03:26 GMT

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?

Re: Button trigger to set select dates in next week

OmarBenSalem — Wed, 22 Nov 2017 15:12:15 GMT

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

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 15:16:48 GMT

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?

Re: Button trigger to set select dates in next week

Mark_Little — Wed, 22 Nov 2017 15:17:26 GMT

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

Re: Button trigger to set select dates in next week

OmarBenSalem — Wed, 22 Nov 2017 15:18:32 GMT

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

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 15:31:09 GMT

HI Omar,

Next_Review_Date

Re: Button trigger to set select dates in next week

OmarBenSalem — Wed, 22 Nov 2017 15:43:02 GMT

in your action button:

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

in your script:

change your Next_Review_Date field to:

date(Next_Review_Date) as Next_Review_Date

or date(date#(Next_Review_Date)) as Next_Review_Date

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 15:45:54 GMT

HI Mark,

Lots of fun!!

The date format in the file and as set in the load is definitely DD/MM/YYYY. I've added the

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

and reloaded but this makes all my dates in both the table and list box disappear (replaced by '-').

Re: Button trigger to set select dates in next week

OmarBenSalem — Wed, 22 Nov 2017 15:56:55 GMT

maybe

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

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 16:00:43 GMT

Hi Omar,

Have changed the button syntax and used

date(Next_Review_Date) as Next_Review_Date

in the load, button action still does nothing.

changed load to

date(date#(Next_Review_Date)) as Next_Review_Date

this removes the date all together.

I've attached the qvw for info, might be easier to look at it first hand.

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 16:03:23 GMT

This just wipes all the dates out

I've attached a copy of the qvw above, hoping this might help?

Re: Button trigger to set select dates in next week

jameswills — Wed, 22 Nov 2017 16:11:16 GMT

Just spotted an error in the data

The syntax from Omar and Mark works a treat when you actually correct the source data!!!!