GetFieldSelections with set analysis

[#1]

I am trying to display YTD(Year to Date) which should show the value for "all" the months for the selected year.

=num(Avg ( {$<Year={"$(=getfieldselections(Year))"}, Month={"*"}>} [S14]) *100)

It is showing the right value if am selecting only Year.

If is select any months or quarters, the value is changing. This should not happen.

Regards

Siva

[#2]

This is my complete expression.

= num(Avg ( {$<Year={"$(=getfieldselections(Year))"}, Month={"*"}>} [S14]) *100 ,0.00)

[#3]

hi ,

Try this

= num(Avg ( {$<Year={"$(=getfieldselections(Year))"}, Month={"$(=getfieldselections(Month))"}>} [S14]) *100 ,0.00)

Best of luck

[#4]

Satish,

Thanks for your reply. but this didn't work.

i want to display the average value of the field (ex: avg(S14)) for a year( i mean for the selected year).

this value should not change even if i select any other list boxes in the sheet (like: Month, Quarter, Name, etc..).

The output value should only reflect for the selections made on the year column.

Hope this helps you to understand my requirement.

Regards

Siva

[Gysbert_Wassenaar]

Try: =num(Avg ( {$<Year={"$(=only(Year))"}, Month=,Quarter=>} [S14]) *100)

[#6]

Thanks for your reply.

No luck.

Both the expressions are giving the same value:

num(Avg ( {$<Year={"$(=Only(Year))"}, Month=>} [S14]) *100 ,0.00)

num(Avg ([S14]) *100 ,0.00)

[flipside · Accepted solution]

Hi,

If you want to return avg for more than 1 selected year, you will need to put the GetFieldSelections through a variable as it needs the quote marks adding in, eg ...

variable = =Replace(GetFieldSelections(Year),', ','","') ( - the quotes at the end spread out are ... (Year), ',' , ' "," ')

then reference this in your expression ...

=num(avg({1<Year={"$(variable)"}>} S14),'0.00')

If you only need to return for one year at a time then this should do the trick, multiple years will not work ...

=num(avg({1<Year={'$(=getfieldselections(Year))'}>} S14),'0.00')

flipside

[#8]

Excellent.....

Its working now.....

Thanks a lot for your timely reply.....

Regards

Siva

[#9]

hi

use sum({$<Years = E({<Years={$(=getfieldselections(Years))}>}Years)>} Sales_s) for Except year that you select show remaning the year in list box

[#10]

HI,

I have got a scenario where in when I select yearmonth range for example 201301 to 201306

then I need to sum(key) where in

resultant should be like for

201301 -> postyearmonth should consider from 201301 to next 5months i.e 201305

I have written like sum({<postyearmnt={">=$(=yearmonth) <=$(=addmonths(yearmonth,5))"}>}Key)

like this for 201302 also and go on

values are correct when I selected only single yearmonth then I am getting correct values

but when I select multiple yearmonths then postig yearmnt starting from repective yearmonth only but postingyearmnt end is not changing for 201302 ,201203 ,201304 ,201305 ,201306 .

it is fixed to 201305 .

please suggest me solution for this.

Thanks in advance.

$@M