13 Replies Latest reply on Dec 14, 2015 5:44 AM by Shinichiro Murakami

# Get value from row where <dimension> == <filter_bound>

I have a data set that looks like this:

State    | Year | Value

Alabama  | 1995 | 20

Alabama  | 1996 | 25

Alabama  | 1997 | 23

Alabama  | 1998 | 26

Alabama  | 1999 | 33

[...]

I've got a range of values filter on year, from 1995 to 1999, and this is all driving a filled map (which shows as color the sum of Value for the filtered range). I also want to show the Value for the lower bound year and upper bound year in a tooltip. In other words, if the filter currently is set to 1997 - 1999, I want the tooltip to display the Value corresponding to 1997 and 1999 (for Alabama, 23 and 33). Something like:

_____________________

| State:      Alabama |

| 1997 Value: 23      |

| 1999 Value: 33      |

|_____________________|

How can I do this?

EDIT: Attached a minimal example of the data I'm working with

Message was edited by: Christopher Berman

• ###### 1. Re: Get value from row where <dimension> == <filter_bound>

Christopher,

If you are OK to use parameter instead of filter (Still can work like filter), I can propose as attached.

Create two parameter from Year Field.

Then put calculated field, then put one more set of Level of detail calculation field.

[Year_start]

iif([Year]=[Start Year],[Value],0)

[Year_End]

iif([End Year]=[Year],[Value],0)

[Sum(Year_Start)]

{exclude[Year]:sum([Year_start])}

[Sum(Year_End)]

{exclude[Year]:sum([Year_End])}

Then put those into tooltip

1 of 1 people found this helpful
• ###### 2. Re: Get value from row where <dimension> == <filter_bound>

Using parameters as filters would not be ideal; currently I'm using a multi-value slider, and it's a great interface for the filter, and I'd very much prefer to keep it if possible. I floated the multiple-slider interface and it didn't go over well with the customer.

• ###### 3. Re: Get value from row where <dimension> == <filter_bound>

Christopher,

Tried other approach.

Looks (at least for me)  working as tooltip.

Not sure this is exactly you want, please let me know if you still have issue.

I don't have strong confident this is really universal solution, but hope it could help something.

[Max_Year]

window_max(min([Year_Cont]))

[Max_Value]

LOOKUP(min([Value]),([Max_Year]-min([Year_Cont])))

[STR(Max_year)]

str([Max_Year])

[Min_Year]

window_min(min([Year_Cont]))

[Min_Value]

LOOKUP(min([Value]),-(min([Year_Cont])-([Min_Year])))

[STR(Min_Year)]

str([Min_Year])

Dual side filtering is reflected in tooltip.

[Tooltip]

State: <State>

<AGG(STR(Min_year))> Value: <AGG(Min_Value)>

<AGG(STR(Max_year))> Value: <AGG(Max_Value)>

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Get value from row where <dimension> == <filter_bound>

Thanks for the follow-up! This covers my use case very well!

To summarize for my understanding, LOOKUP allows us to pluck out a piece of non-aggregate data from the backing data set, and the trick can be figuring out how to write the LOOKUP expression (sometimes requiring single-purpose calculated fields). This was the piece I was missing.

Thanks again!

• ###### 5. Re: Get value from row where <dimension> == <filter_bound>

I think your understanding is correct.

I'm also learning many things through request - answer loop.

Thanks,

Shin

• ###### 6. Re: Get value from row where <dimension> == <filter_bound>

I think I spoke too soon (though it's possible I've implemented the solution you've provided improperly, or that I haven't accurately described what I'm doing). It looks like the Min_Value and Max_Value are providing the minimum value and maximum value for a given state within the filter, which may not be the same as the lower bound and upper bound values. For example, if the filter is set to 1996-1999, Min_Value should return 25 (the value associated with 1996, the lower bound), but currently returns 23 (1997's Value, which is the minimum value within the filter range).

I've been trying to adjust the LOOKUP calculations to pull the Value for the filter range (Max_Year and Min_Year work perfectly), but haven't had any luck. I keep running into the "Cannot mix aggregate and non-aggregate arguments" error. It looks like LOOKUP is an aggregation calculation, but I really just want to get the exact, singular value for a given dimension (State, in this case). Is this not possible?

• ###### 7. Re: Get value from row where <dimension> == <filter_bound>

Christopher,

Could you give me a certain volume of sample data set?

Text in this screen or excel attachment, whatever works for you.

It's easier to talk, with sharing expected number.

Thanks,

Shin

• ###### 8. Re: Get value from row where <dimension> == <filter_bound>

Absolutely; thanks again for all the help!

This is a minimal example of what I'm working with.

• ###### 9. Re: Get value from row where <dimension> == <filter_bound>

So, your request is to show the tooltips on the map view, not table view, correct?

I think that because "window_sum" only works on the table view, I think I need to make another challenge to show the tooltips as you want on the map. : )

Thanks,

Shin

• ###### 10. Re: Get value from row where <dimension> == <filter_bound>

The data is driving a bar graph too, and I was hoping to use the tooltips there as well. I didn't realize value lookups like this had to be custom-fitted for a given graph. At this point, I'm mostly just looking for a pointer in the right direction (reading through the manual atm)

• ###### 11. Re: Get value from row where <dimension> == <filter_bound>

Christopher,

Could you check attached file.

Looks working to me.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 12. Re: Get value from row where <dimension> == <filter_bound>

It turns out I needed Level of Detail Expressions. Using these, I was able to simply and easily get exactly the data I wanted, even though it wasn't available at the view level. In this case, I needed to use curly braces as well as the INCLUDE keyword, so that the calculation would be constrained by the filter (leaving out a keyword assumes 'FIXED', which ignores the filter). This produces a non-aggregate field, which avoids the error of comparing aggregate and non-aggregate fields. Very powerful!

Attached is the minimal workbook linked earlier with the new fields. Thanks for working through this with me, Shinichiro!

• ###### 13. Re: Get value from row where <dimension> == <filter_bound>

Sounds good!.

Shin