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

[Gysbert_Wassenaar]

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

[danosoft]

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

[olivierrobin]

hello

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

[danosoft]

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)

[olivierrobin]

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 ?

[Miguel_Angel_Baeyens]

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

[danosoft]

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

[danosoft]

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

[Miguel_Angel_Baeyens]

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.