Something obvious with my 'Set' formula I'm just not seeing?

[#1]

Dear All,

I've having trouble trying to get a Set condition working with a variable for about 5 hours already.

Wondering if someone can shed some light on what's wrong with the following

//sum({$<Year = {2013}>}[Value]) ** This works

//sum({$<Year = { $(=Max(Year)) } >} [Value])  ** This works

//sum (if (Year = $(thisYear), Value)) ** This works

//sum({$<Year = {$(=thisYear) } >} [Value])  //** This not so much !

//sum({$<Year = {$(thisYear) } >} [Value])  //** Nor this

I'm trying to just Sum the [Value] field for the current year.

The thisYear field is defined as follows

SET thisYear = Year(Now());

If I display thisYear in a text field it displays properly

I'm assuming the Set command is reading the literal "thisYear" rather than the value contained in "thisYear".

From my surfing around it looks right but it's obviously not or it would be working

Thanks for any assistance you can provide.

[#2 · Accepted solution]

sum({$<Year = {"$(=$(thisYear))"}>} Value)

[Sokkorn]

Hi stejim0,

After read through previous thread, yes, it could be "Type". Troubleshoot:

1. Sum({$<Year = {2013}>} [Value])  ==> Tell me it work or not?

2. Sum({$<Year = {"2013"}>} [Value])  ==> Tell me it work or not?

3. Sum({$<Year = {$(=Year(Today()))}>} [Value]) ==> Tell me it work or not?

4. Check Field [Year] to find data type.

Regards,

Sokkorn

[#4]

Hi Sokkom,

The answers to points 1,2,3

//Sum({$<Year = {2013}>}[QuantityField]) //This works

//Sum({$<Year = {"2013"}>}[QuantityField]) //This also works

//Sum({$<Year = {$(=Year(Today()))}>}[QuantityField]) //And this also works

Question 4 I don't know how to find out that answer, some guidance would be appreciated.

Regards,

Jim

[#5]

Many thanks

This works

sum({$<Year = {"$(=$(thisYear))"}>} QuantityField) //**Awesome this work

Interestingly this also works

sum({$<Year = {$(=$(thisYear))}>} QuantityField) //Interestingly this also works so Year must be numeric.

If you have time to explain your logic I would appreciate it, though I'm accepting your answer in any event.

[#6]

Hi Sokkom,

Someone else above managed to get it working for me, so no need for you to research further.

Thanks,

Jim

[Sokkorn]

Hi Jim,

Create one ListBox for [Year] field. And check value alignment. By default if value is at Right alignment, then [Year] is number fomat else if at Left is Text fomat.

Anyhow, did you solve your issue yet?

Regards,

Sokkorn

[calvindk]

sum({$<Year = { $(=Year(Now())) } >} [Value])

i missed () in the now

its the same as the answer marked correct now, which is the double expansion i was talking about, but there is really no reason to put that part in a variable.