Value from an unselected filter

[Anonymous]

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

[MayilVahanan]

HI

Try like this

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

or

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

[#3]

here I leave an example:

[Anonymous]

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

[Anonymous]

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

[#6]

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

[jvitantonio]

Hi Luca,

Please see the solution in the attached app.

JV

BI Experience | A place to share our Business Intelligence experiences

[Carlos_Reyes]

Like this ?

[Anonymous]

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.

[Anonymous]

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.