7 Replies Latest reply on Jan 9, 2017 2:37 PM by Yuriy Fal

# Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Hi

I've currently created a reference line for a scatter plot with customized static data using a parameter but would like the reference lines to update on its own. I.e. if I change date filter to 2017F, can I get the reference line to take on updated data as well?

The data points I would like to use for the reference line would be a calculated field (5YF UO Rental Growth) but filtered by State (DIM) and Year (DIM) (to USA and relevant year).

Any help or suggestions will be greatly appreciated!

Workbook attached. Thanks!

• ###### 1. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Hi Jingwei,

Why not just use the [5YF Avg UO Rental Growth] --

the Average of 5YF Averages -- as a RefLine?

Do the numbers of 3.5% for 2016F or 3.6% for 2017F look good for you?

If not, what the expected values would be, and more important,

what the underlying calculation / filtering logic would be?

Yours,

Yuri

• ###### 2. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

I would like to use weighted average (rental growth * no. of beds in each market) which is pre-calculated under 'USA' in my data. If I use Average of 5Y averages, I get a simple average (3.48% and 3.65% for 2016F and 2017F). But yes that would change with years automatically. Is there maybe a hack to set up a parameter with an equation? Or set up a reference line with filters/equation?

Expected value for 2016F Rental Growth Ref Line: 3.43% i.e. USA, filtered to 2016F

Expected value for 2017F Rental Growth Ref Line: 3.64% i.e. USA, filtered to 2017F

The difference isn't big here because of a largely homogenous sample. But I can imagine scenarios where weighted average and simple average yield significantly different results.

Thanks!

• ###### 3. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Hi Jingwei,

Since I couldn't reconstruct the [UO Rental Growth]

USA values from the States figures, I just pushed

the USA values (already in the dataset) to each State

(using FIXED & EXCLUDE LOD combo).

Hope it would be of help.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 4. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Thank you Yuriy! This works perfectly for Rental Growth!

I duplicated your FIXED & EXCLUDE LOD for Occupany Change but can't figure out why I'm not getting the expected results as in 'UO Occ' tab? I seem to be pulling up Avg Occ Chg of one year only despite doing Window_sum for 5Y?

Your help in troubleshooting is much appreciated.

• ###### 5. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Hi Jingwei,

What are the expected results for 2016F and 2017F?

If they're like -0.06% and +0.07% respectively, then you'd like

to set the [YF : Weighted OC] Table Calc Pill on Details

to be Addressing along (Compute using =>) [Year] --

just the same as for the [YF : Weighted RG] table calc pill.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 6. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Thank you, Yuri! This solved the issue!! And I learnt something new

• ###### 7. Re: Reference Line with Calculated Field, Filtered by Dimension on Scatter Plot

Jingwei, you're welcome.