10 Replies Latest reply: Dec 28, 2016 7:58 AM by Marcus Augusto

# GetFieldSelections with set analysis

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

• ###### Re: GetFieldSelections with set analysis

This is my complete expression.

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

• ###### Re: GetFieldSelections with set analysis

hi ,

Try this

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

Best of luck

• ###### Re: GetFieldSelections with set analysis

Satish,

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

• ###### Re: GetFieldSelections with set analysis

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

• ###### Re: GetFieldSelections with set analysis

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)

• ###### Re: GetFieldSelections with set analysis

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

• ###### Re: GetFieldSelections with set analysis

Excellent.....

Its working now.....

Regards

Siva

• ###### Re: GetFieldSelections with set analysis

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.

\$@M

• ###### Re: GetFieldSelections with set analysis

This help me!

tks

• ###### Re: GetFieldSelections with set analysis

hi

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