Dynamic Target Line for Bar Chart based on a Bookmark

[#1]

Hello All,

I am trying to create a target line for my bar chart based on year 2012 to compare against data prior and post 2012. I have created the bookmark BM2012 to help. What I am expecting from my formula is that the target line remains the same for any given set of listbox selections where the only difference is the year selected. 

To illustrate, my intention is that:

The target line should stay the same for these two selections:

Year:2011-12
Department: BBB
Section: 123
Customer: AAA

Year:2012-13
Department: BBB
Section: 123
Customer: AAA

But likely be different for the following:

Year:2011-12
Department: BBB
Section: 123
Customer: AAA

Year:2011-12
Department: BBB
Section: 123
Customer: CCC

The code I am using is the same as the bar chart and is:

=sum(aggr(sum({BM2012}{$<[L O S] = {">= $(vSlider)"}>}Distinct [L O S]), ZNumber)) / count({BM2012}{$<[L O S] = {">= $(vSlider)"}>} Distinct ZNumber )

Thanks in advance

[#2]

Bump

[hic]

It's impossible to say what the correct expression should be without having the data, but I can say the following:

1) You have duplicate Set Identifiers: {BM2012}{$<...>}  This will not work. Use {BM2012<...>} instead.

2) If you want to combine the bookmark with the current selection, you should probably do it using implicit field value definitions: {BM2013<Field=P({$} Field)>}

HIC

[#4]

Hi,

Thanks for this. I am struggling to get the expression to validate. Here is the first part:

= sum(aggr(sum( {BM2012 <[LOS] = P({">= $(vSlider)"}> [LOS])} Distinct [LOS]), zNumber))

[Gysbert_Wassenaar]

Perhaps this:

= sum(aggr(sum( {BM2012 <[LOS] = {">= $(vSlider)"}>} Distinct [LOS]), zNumber))

[#6]

G Wassenaar (to carl carl) 18 hours ago

Perhaps this:

= sum(aggr(sum( {BM2012 <[LOS] = {">= $(vSlider)"}>} Distinct [LOS]), zNumber))

Thanks for your input. The full expression generates a value of "0" and that shouldn't be the case.

= sum(aggr(sum( {BM2012 <[L O S] = {">= $(vSlider)"}>} Distinct [L O S]), zNumber)) / count({BM2012 < [L O S] = {">= $(vSlider)"}>} Distinct zNumber)