Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
This is my complete expression.
= num(Avg ( {$<Year={"$(=getfieldselections(Year))"}, Month={"*"}>} [S14]) *100 ,0.00)
hi ,
Try this
= num(Avg ( {$<Year={"$(=getfieldselections(Year))"}, Month={"$(=getfieldselections(Month))"}>} [S14]) *100 ,0.00)
Best of luck
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
Try: =num(Avg ( {$<Year={"$(=only(Year))"}, Month=,Quarter=>} [S14]) *100)
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)
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
Excellent.....
Its working now.....
Thanks a lot for your timely reply.....
Regards
Siva
hi
use sum({$<Years = E({<Years={$(=getfieldselections(Years))}>}Years)>} Sales_s) for Except year that you select show remaning the year in list box
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.