topic Re: Dynamic Target Line for Bar Chart based on a Bookmark in QlikView

Dynamic Target Line for Bar Chart based on a Bookmark

Thu, 17 Oct 2013 08:44:15 GMT

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

Re: Dynamic Target Line for Bar Chart based on a Bookmark

Thu, 17 Oct 2013 10:21:56 GMT

Bump

Re: Dynamic Target Line for Bar Chart based on a Bookmark

hic — Thu, 17 Oct 2013 12:18:15 GMT

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

Re: Dynamic Target Line for Bar Chart based on a Bookmark

Thu, 17 Oct 2013 16:08:57 GMT

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))

Re: Dynamic Target Line for Bar Chart based on a Bookmark

Gysbert_Wassenaar — Thu, 17 Oct 2013 17:00:47 GMT

Perhaps this:

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

Re: Dynamic Target Line for Bar Chart based on a Bookmark

Fri, 18 Oct 2013 10:38:58 GMT

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)