2 Replies Latest reply: Jan 4, 2012 11:58 AM by Marc Livingston RSS

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

      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)