topic Re: syntax of a trigger in QlikView

syntax of a trigger

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

Hello everybody,

I am looking for some rules about syntax needed to set a trigger. Are there some plain guidelines?

For example: I have made a variable vInvoiceweek, defined as Num(week([Invoicedate]),'00')

Now I want to make a button that selects the last week as invoiceweek (to see what is invoiced last week)

I like to know:

-Do I need a dollar sign in the triggerfield (eguals sign is not needed)

-Do I need quotes in the search text? Do I need the equals sign?

In case of my example:

Field: vInvoiceweek + Searchtext: week(now())-1 Does not work.

Searchtext between quotes also not. Field with a dollarsign also not. Searchtext with =sign also not. And so I can make some more combinations...

Please can anyone give some explanation (maybe by using this example) (I don't need only a correct answer for this example, but I would like an explanation why something works or doesn't work also).

There is a good trilogy of Henric Cronström about 'The little equals sign' , 'The magic of Variables' and 'The magic of Dollar Expansions' (https://community.qlik.com/blogs/qlikviewdesignblog/2014/11/24/the-equal-sign

but that is a bit too complicated (maybe in the future )

Re: syntax of a trigger

jharke — Thu, 30 Jun 2016 14:51:02 GMT

See attached document as example

Re: syntax of a trigger

rubenmarin — Thu, 30 Jun 2016 15:38:22 GMT

Hi jharke, trying to explain with an example like selecting customer code, I only set as example the search box

equal signs

Tells QV to evaluate first the expression and then apply the result, if you don't set the equal sign it will try to select exactly what you set in the box

=1888+1 // Will select the customer 1889

1888+1 // Tries to select the customer code 1888+1 (like that, with the plus sign and everything

There are some boxes like the expressions wich always includes the equal sign at start, you don't need to add it

dollar sign

Tells qv to evaluate the expression inside the $() and applies the result to the rest of the expression.

=$(=1888+1) // Is tranformed to =1889, so the customer code 1889 is selected

$(=1888+1) // Tries to select the customer code $(=1888+1)

Re: syntax of a trigger

antoniotiman — Thu, 30 Jun 2016 16:56:35 GMT

Hi Jharke,

Your field is Invoicedate. vInvoiceweek is variable.

Try

Re: syntax of a trigger

jharke — Fri, 01 Jul 2016 12:11:13 GMT

Hello Antonio

It was my intention to use a variable. The idea is to choose last week simple with week(now())-1 as vInvoiceweek.

If I use your formula, I get all the dates, see printscreen.

Why is the formula with quotation marks? I think that is the reason that the outcome is given as text now.

And I also think 'Floor' is not needed.

At least the outcome when I press the button is something :-), but not good 😞

Re: syntax of a trigger

jharke — Fri, 01 Jul 2016 13:49:15 GMT

Hello Ruben,

An association with your reaction:

I have tried something with a simple field: Year. f I want year 2016, following combinations in the Search string of the trigger can be filled (and some of them will work)

2016 -->OK

=2016 -->OK

'2016' --> nothing

='2016' -->OK

(2016) -->nothing

2017-1 -->nothing

'2017-1' -->nothing

='2017-1' -->nothing

=2017-1 -->OK

(2017-1) -->nothing

=(2017-1) -->OK

'(2017-1)' -->nothing

='(=2017-1)' --> strange outcome: (=2017-1) as value in the current selection box? That "value" doesn't even exists! How can QV a non-existing outcome? And with all the orther quoted combinations do not have a strange outcome...

$(2016) -->nothing! --> it seems,the dollarsign is forcing QV to see a formula, while without $() the search string for sure is OK.

$(=2016) -->also nothing?

=$(=2016) -->OK

$(=2017-1) -->OK

After these trials I have changed the search string in my example of vInvoiceweek.

My Variable:

This variable is working: I can get a List Box of this one.

My Trigger:

It doesn't work! Even if I fill =22 in the search string, it is not working. What am I doing wrong?

Re: syntax of a trigger

antoniotiman — Fri, 01 Jul 2016 16:51:28 GMT

I'm sorry

='>=$(=Floor...................................

Re: syntax of a trigger

rubenmarin — Sat, 02 Jul 2016 08:07:24 GMT

Sorry, I explained some differences and I forgot to answer the question.

As stated by Antonio, the first box has to be a Field name (I wasn't aware it is a variable name), or an expression (starting by an equal sign) that returns a field name.

There is another action in 'External' group called set variable, wich is used to assing a value to a variable. Note that a variable only stores one value, so if you set =week(now()) the variable will store the week number. (losing the expression it has stored before)

From your tries: all without an starting equal sing is trying to select exactly what you insert in the Search String box. I'm surprised with this one: $(=2017-1) I supposed it will try to select the value '$(=2017-1)' (literally, as is not started by an equal sign before the dollar sign).

The outcome for ='(=2017-1)'. ='$(=2017-1)' should be expanded to ='2016' wich will select 2016 but I don't know what is doing without the dollar.

So, in the Field box you can set Week, InvoiceWeek... the field storing the week of the invoice.

And as Search String box you can set:

=Week(now()-7) // You can set Today() instead of Now()

I substract 7 days from today and then I obtain the week of that date. If you substract 1 from the week, on the week 1 it will fail trying to select week 0.

And if you want to select the dates instead of the week the solution Antonio gives should work.

Re: syntax of a trigger

rubenmarin — Sat, 02 Jul 2016 08:19:42 GMT

In this exaple Invoice Week is not a field, it is a list created on a calculation based on the Invoicedate field, and you can't select values in a variable, the values are selected in fields, when you click on an item in the Invoice Week List it will make selections on Invoicedate, because it's the field used in the expression of that list.

The use of the variable here is working as an expression stored in a variable, it only cotains the text "

Num(Week(Invoicedate),'00')" and not any of the values.

This use case is good if you want to use the same expression in different objects: if at some point you want to use another field to obtain the week you only need to change the field in the variable and all the objects using the variable will be updated. It saves you to update the expression of every object.

Re: syntax of a trigger

jharke — Mon, 04 Jul 2016 12:02:00 GMT

Hello Antonio,

The formula is working.

But why do I have to use the FLOOR function?

If I leave it, the trigger doesn't work, but I would think that the command : "More-then-or-equal-to WeekStart of today, shifted with minus 1 AND less-then-or-equal-to WeekEnd of today, sifted with minus one" would be enough?

Like: ='>=$(=WeekStart(Today(),-1))<=$(=WeekEnd(Today(),-1))'

Re: syntax of a trigger

antoniotiman — Mon, 04 Jul 2016 13:41:35 GMT

Hi,

Foor() takes integer of WeekStart/WeekEnd, so it Compares with integer Date.

If You remove this

WeekStart(Today) -> 04/07/2016 and this in Expressio needs quotes '04/07/2016'.

Regards,

Antonio

Re: syntax of a trigger

jharke — Mon, 04 Jul 2016 14:19:13 GMT

Thank you

I did not know this Flooring function. Due to your post I went searching and learned a lot about setting dates. FLOOR can be used to make integer of dates. In my case it was not necessary, because the dates are already integers (timestamp 0:00:00).

If I use NUM in stead of FLOOR, it also works.

If I 'force' the expression to be a number (something I use a lot in Excel) the outcome gives me also the correct week and is a bit shorter, but gives a strange 'current selections'. If someone can tell me what that 999..88 means, I'd be glad...

Nice post about setting dates and understanding FLOOR:QlikView Date fields

Re: syntax of a trigger

antoniotiman — Mon, 04 Jul 2016 15:51:55 GMT

Num(WeekEnd( has decimal portion.

so You need to use Floor()