Script for filter by field Data

[danosoft]

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

[danosoft]

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

[frank1982 · Accepted solution]

Hi Daniele,

try this

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

[danosoft]

:-0000 TanaVigliaca!!

Great Franco, it works!!

Thanks you

[Miguel_Angel_Baeyens]

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