topic Re: Value from an unselected filter in QlikView

Value from an unselected filter

Anonymous — Mon, 04 Nov 2013 14:26:59 GMT

Hi everybody,

i have a DB table with the following fields:

IDPARK , YEAR, MONTH, VALUE

1 ,2011 ,1 ,4234

1 ,2011 ,2 ,3453

1 ,2011 ,3 ,45745

1 ,2013 ,7 ,768

1 ,2013 ,8 ,989

1 ,2013 ,9 ,34

in a PIVOT table i use IDPARK as Dimension and VALUE as Expression.

If i select a YEAR and a MONTH from a filter everything works good and the table show the right value; but i would like that when i do not select a filter, the table show me the VALUE linked to the max(makedate(YEAR,MONTH)) . In this case September 2013.

Or if i select from a filter YEAR = 2011 show me the value of March 2011 .

Is it possible?

Thanks

Re: Value from an unselected filter

MayilVahanan — Mon, 04 Nov 2013 14:51:44 GMT

Try like this

=If(Isnull(GetCurrentSelections()), exp1, exp2)

= if(GetFieldSelections(Year) = 2011, Sum({<MONTH = {3}>}Value), Sum(Value))

Re: Value from an unselected filter

Mon, 04 Nov 2013 15:14:29 GMT

here I leave an example:

Re: Value from an unselected filter

Anonymous — Mon, 04 Nov 2013 16:53:05 GMT

i do not really aunderstand your solutions.

in the first: if i had others selection that aren't involved with this field, what going to happend?

in the second: i can' specified a YEAR like 2011

Re: Value from an unselected filter

Anonymous — Mon, 04 Nov 2013 16:56:09 GMT

In my table i visualize every month of every year with your method, putting YEAR and MONTH as Dimension.

can i avoid to use them as dimensions?

If i have no selection, nothing will be show

Re: Value from an unselected filter

Mon, 04 Nov 2013 17:03:59 GMT

If you want to show you the single value, you can remove the dimensions of Month and year

Re: Value from an unselected filter

jvitantonio — Mon, 04 Nov 2013 17:20:13 GMT

Hi Luca,

Please see the solution in the attached app.

BI Experience | A place to share our Business Intelligence experiences

Re: Value from an unselected filter

Carlos_Reyes — Wed, 06 Nov 2013 23:05:15 GMT

Like this ?

Re: Value from an unselected filter

Anonymous — Thu, 20 Mar 2014 15:18:49 GMT

If i add to the LOAD

1 ,2014 ,1 ,4

1 ,2014 ,2 ,1

MAX (YEAR) and MAX (MONTH)

try to return the value of YEAR 2014 MONTH 9 that doesn't exist.

I found the function FIRSTSORTEDVALUE that works good for my goal.

Re: Value from an unselected filter

Anonymous — Thu, 20 Mar 2014 15:18:56 GMT

If i add to the LOAD

1 ,2014 ,1 ,4

1 ,2014 ,2 ,1

MAX (YEAR) and MAX (MONTH)

try to return the value of YEAR 2014 MONTH 9 that doesn't exist.

I found the function FIRSTSORTEDVALUE that works good for my goal.

Re: Re: Value from an unselected filter

Carlos_Reyes — Thu, 20 Mar 2014 15:32:02 GMT

You can do that, or you can add a Date and/or a MonthYear field and still use Set Analysis.

Review the attached.