topic Re: Script for filter by field Data in QlikView

Script for filter by field Data

danosoft — Mon, 04 Jun 2018 15:36:32 GMT

Hi i need to take on my straight table all the rows where satisfy my filter:

=Sum({1<DATA_IN={'>28/02/2018'}>} CONSUMO_TOT_ANNUO)

if i write this is all ok, and the table returns the right rows.

Now i have this variable:

Set vFineMonthContr = MonthEnd(MakeDate(GetFieldSelections(ANNOOUT),Month(Date(Date#(GetFieldSelections(MESEOUT),'MM')))));

It take the last date of my selection fields, for example i have selected from my list box:

Year : 2018 and Month: 02

so now when i put my set analisys script in my expression, it return NO rows... where is the problem in the script?

=Sum({1<DATA_IN={">=$(vFineMonthContr)"}>} CONSUMO_TOT_ANNUO)

or i tried

=Sum({1<DATA_IN={">=$(=vFineMonthContr)"}>} CONSUMO_TOT_ANNUO)

but same it return NO ROWS

Re: Script for filter by field Data

Gysbert_Wassenaar — Mon, 04 Jun 2018 16:06:11 GMT

Set analysis only works in front end expressions. It cannot be used in the script.

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 06:54:17 GMT

It is not in the script, is in in Expression in my straight table like i wrote. Thanks

Re: Script for filter by field Data

olivierrobin — Tue, 05 Jun 2018 07:04:34 GMT

hello

try to put your variable in a text box to see if the value is in the correct format

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 07:25:43 GMT

Hi, i did yet, the value is correct: =$(vFineMonthContr) 28/02/2018

where is the problem in my expression becouse is return NO rows?:

=Sum({1<DATA_IN={">=$(vFineMonthContr)"}>} CONSUMO_TOT_ANNUO)

or i tried

=Sum({1<DATA_IN={">=$(=vFineMonthContr)"}>} CONSUMO_TOT_ANNUO)

Re: Script for filter by field Data

olivierrobin — Tue, 05 Jun 2018 07:30:24 GMT

the difference between the expression that works and the other on is that in the 1st case, you use single quotes and in the 2nd, you use double .

did you try with single quotes ?

Re: Script for filter by field Data

Miguel_Angel_Baeyens — Tue, 05 Jun 2018 07:37:12 GMT

When using dates, try to use the Date() function to make sure the value you are looking for has the right format:

=Sum({1<DATA_IN={">=$(=Date('$(vFineMonthContr)', 'DD/MM/YYYY'))"}>} CONSUMO_TOT_ANNUO)

The format mask might not be needed. I might have missed some parentheses also

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 07:41:28 GMT

Yes, and i have the same problem with the single quote.

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 07:44:07 GMT

Hi, thanks, in this way it come back all rows in the table, it not do the filter for the DATA_IN

Re: Script for filter by field Data

Miguel_Angel_Baeyens — Tue, 05 Jun 2018 07:48:51 GMT

Looks like the variable vFineMonthContr is not being expanded correctly as you expect.

I would then suggest to create a numeric field in the calendar instead of a full date for this type of comparisons. Troubleshooting variables within variables is not easy, and without having the document in front, there could be an "=" sign which makes the difference. Or a LET instead of a SET. The ">=" operator requires a number, and 28/02/2018 is not a number, but Date('28/02/2018') is, if DD/MM/YYYY is your server, user and document locale configuration.

Much simpler creating a DateNum field in the script where your minimum date possible is 1 and it increases sequentially.

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 07:51:22 GMT

This is really interesting Miguel, can you write there an example for do in the script the DateNum field? so i can try to do that, thanks

Re: Script for filter by field Data

frank1982 — Tue, 05 Jun 2018 07:55:32 GMT

Hi Daniele,

try this

=Sum({1<DATA_IN={">=$(=Date($(vFineMonthContr), 'DD/MM/YYYY'))"}>} CONSUMO_TOT_ANNUO)

Re: Script for filter by field Data

danosoft — Tue, 05 Jun 2018 07:57:43 GMT

:-0000 TanaVigliaca!!

Great Franco, it works!!

Thanks you

Re: Script for filter by field Data

Miguel_Angel_Baeyens — Tue, 05 Jun 2018 08:01:15 GMT

Dates are numeric dual values, values which can be represented both as a number and as a string.

In my case, the document format for dates is DD.MM.YYYY (Switzerland local), so

Date('05.06.2018' +1) returns 06.06.2018. Num(Date('05.06.2018')) returns 43256

However,

Date('05/06/2018' +1) returns null, as that is understood by QlikView as a literal or string, but not a number, and the Date() function expects a numeric parameter first.

Doing that (on a second thought, forgetting my previous suggestion)

Num(Date(DateField)) AS DateNum

in the script will store the numeric representations of the dates, simplifying the comparisons a lot by using that DateNum field in all the expressions, and the DateField when you need the literal representation. Another good side effect on this is that you can also use any date function on those values, like MonthEnd(), AddMonths() and any other.

For example,

MonthEnd(43256) will return in my case 30.06.2018