Replicating Sales Overlap Chart from "What's New in Qlikview11" dashboard

[#1]

I have had a request to add this Sales overlap chart to a dashboard with our data. Unfortunately there is not a very good example on how to properly set this up.

This is what I currently have as the chart expression:

='http://chart.apis.google.com/chart?cht=v&chs=330x220&'&
//'chtt=Invoices shared among products&' & 
'chdl=' & 'Base'& '|' & 
'x' & 'Group 1' & ' = ' & 
count({$<[Order Number] = p({$} [Order Number]) * p({[Group 1]< [Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number]) & 
'|x' & 'Group 2' & ' = ' & 
count({$<[Order Number] = p({$} [Order Number]) * p({[Group 2]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number]) &  //' shared' & 
'&' &
'&chco=FFCC00,9AB2CD,BDCBB3&'&
'chd=t:'&
round(count({$} DISTINCT [Order Number])/vMaxSetScale,1)&','&
round(count({[Group 1]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} DISTINCT [Order Number])/vMaxSetScale,1)&','&
round(count({[Group 2]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} DISTINCT [Order Number])/vMaxSetScale,1)&','&
round(count({$<[Order Number] = p({$} [Order Number]) * p({[Group 1]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number])/vMaxSetScale,1)&','&
round(count({$<[Order Number] = p({$} [Order Number]) * p({[Group 2]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number])/vMaxSetScale,1)&','&
round(count({[Group 1]<[Order Number] = p({[Group 1]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number]) * p({[Group 2]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number] )/vMaxSetScale,1)&','&
round(count({$<[Order Number] = p({$} [Order Number]) * p({[Group 1]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number]) * p({[Group 2]<[Territory] = $::[Territory], [Year Ordered] = $::[Year Ordered], [Month Ordered] = $::[Month Ordered]>} [Order Number])>} DISTINCT [Order Number])/vMaxSetScale,1)

But this is not working how the example they give works.  Group 1 no matter what I select in the listbox set to the Group 1 State never changes.

Am I doing something wrong?

I have the base selection set to default state, Group 1 set to Group 1 state, and Group 2 set to Group 2 state.

Selections made in the base and in Group 2 are reflected on the chart.

Here is the examples expression:

='http://chart.apis.google.com/chart?cht=v&chs=330x220&'&
//'chtt=Invoices shared among products&' & 
'chdl=' & Minstring({<Index = {73}>} [$(vLanguage)])& '|' & 
'x' & Minstring({<Index = {69}>} [$(vLanguage)]) & ' = ' & 
count({$<[Invoice Number] = p({$} [Invoice Number]) * p({[Group 1]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number]) &  //' shared' & 
'|x' & Minstring({<Index = {70}>} [$(vLanguage)]) & ' = ' & 
count({$<[Invoice Number] = p({$} [Invoice Number]) * p({[Group 2]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number]) &  //' shared' & 
'&' &
'&chco=FFCC00,9AB2CD,BDCBB3&'&
'chd=t:'&
round(count({$} DISTINCT [Invoice Number])/vMaxSetScale,1)&','&
round(count({[Group 1]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} DISTINCT [Invoice Number])/vMaxSetScale,1)&','&
round(count({[Group 2]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} DISTINCT [Invoice Number])/vMaxSetScale,1)&','&
round(count({$<[Invoice Number] = p({$} [Invoice Number]) * p({[Group 1]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number])/vMaxSetScale,1)&','&
round(count({$<[Invoice Number] = p({$} [Invoice Number]) * p({[Group 2]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number])/vMaxSetScale,1)&','&
round(count({[Group 1]<[Invoice Number] = p({[Group 1]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number]) * p({[Group 2]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number] )/vMaxSetScale,1)&','&
round(count({$<[Invoice Number] = p({$} [Invoice Number]) * p({[Group 1]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number]) * p({[Group 2]<Path = $::Path, Region = $::Region, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} [Invoice Number])>} DISTINCT [Invoice Number])/vMaxSetScale,1)

[pover]

I can't see anything obvious that is wrong with your expression when looking at the whole thing.  You might want to change the representation to text instead of image so that you can see the url as below:

http://chart.apis.google.com/chart?cht=v&chs=330x220&chdl=Base|xGroup 1 = 41|xGroup 2 = 77&&chco=FFCC00,9AB2CD,BDCBB3&chd=t:413,2014,5069,41,77,459,22

In this way, you might be able to recognize the exact location of the error and fix it.

Karl

[#3 · Accepted solution]

You are correct. Doing this led me to try changing the Alternate state names. It seems to not like the space.

I changed Group 1 to Group1 and Group 2 to Group2 and it seems to be working now.