Error in Set Expression

[alkesh_sharma]

Hello All,

I am Using the following Expression:

=Sum({<[Revenue.Invoice Date] = {$(vCutoffDate)}>} [Revenue.Invoice Amount]/vMillion)

Its returning me an Error: ERROR in SET MODIFIER, ',' or ')' Expected.

Need Help..

[stabben23]

if u comapare DD-MM-YYYY([Revenue.Invoice Date]) with your vCutoffDate DD-MM-YY you wont have a match.

[jyothish8807]

Hi Alkesh,

Can you share the expression you are using in the variable? It will be helpful .

Regards

KC

[alkesh_sharma]

Yes, Its ''DD-MM-YY' only, I did a typing mistake in the above reply..

[stabben23]

Try this instead {'=(Date(vCutoffDate))'}

[tcullinane]

Try
if([Revenue.InvoiceDate] = vCutOffDate,Revenue.InvoiceAmount)

may give you more information on where its going wrong

could also show both Invoice date and vCutOffDate in the table along with the if statement.

Change both their default number type to decimal also, as this is what the comparison is on, the number value not the display value.

This can cause issue if a time component is still in a date value so make sure to floor() a date when only the date is important and remove the time or decimal component of the day.

[alkesh_sharma]

Figures it out.. there was some date formatting problems. Thanx for your suggestion guys

[jonathandienst]

Hi

This expression in a list box

=$(vCutoffDate)

expands to

=02-11-11

which QV interprets as 2 minus 11 minus 11 = -20

This is because the $() expansion is a text expansion only. It has not concept of data type. Compare that with

='$(vCutoffDate)' // ie with quotes

expands to

='02-11-11'

which Qv might interpret as a date (if DD-MM-YY is the default date format on your system). Otherwise, use

=Date(Date#('$(vCutoffDate)', 'DD-MM-YY'), 'DD-MM-YYYY') in a text box, or

....{"$(=Date(Date#(vCutoffDate, 'DD-MM-YY'), 'DD-MM-YYYY'))"} in a set expression filter.

HTH

Jonathan

[alkesh_sharma]

Thanx Jonathan,

This was indeed helpful.