topic Re: GetFieldSelections with set analysis in QlikView

GetFieldSelections with set analysis

Thu, 06 Dec 2012 10:16:11 GMT

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

Thu, 06 Dec 2012 10:18:22 GMT

This is my complete expression.

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

Re: GetFieldSelections with set analysis

Thu, 06 Dec 2012 10:27:40 GMT

hi ,

Try this

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

Best of luck

Re: GetFieldSelections with set analysis

Thu, 06 Dec 2012 10:36:56 GMT

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

Re: GetFieldSelections with set analysis

Gysbert_Wassenaar — Thu, 06 Dec 2012 10:39:36 GMT

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

Re: GetFieldSelections with set analysis

Thu, 06 Dec 2012 11:08:44 GMT

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)

Re: GetFieldSelections with set analysis

flipside — Thu, 06 Dec 2012 12:25:18 GMT

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

Fri, 07 Dec 2012 10:56:29 GMT

Excellent.....

Its working now.....

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

Regards

Siva

Re: GetFieldSelections with set analysis

Tue, 04 Jun 2013 05:13:46 GMT

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

Re: GetFieldSelections with set analysis

Fri, 12 Dec 2014 10:37:50 GMT

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

Re: GetFieldSelections with set analysis

mvaugusto — Wed, 28 Dec 2016 12:58:31 GMT

This help me!

tks