2 Replies Latest reply: Dec 16, 2014 9:08 AM by Bram Van Helleputte RSS

    How to group by year in a chart with relative values?

    Bram Van Helleputte

      Hi,

       

      I am pretty new to QV and I've been stuck on this line chart that's supposed to give me the evolution of market shares over 3 years.

      values give me the % relative to the total amount over teh 3 years, while I just want the % for each year.

       

      To be more specific, we sell 5 car brands and want to see the market shares of each brand, while the whole market = registrations of the 5 brands, so if no brand is selected the line should stay on 100% for each year.

       

      I've been thinking on something like a group by? But this doesn't seem to work in the script as I use a OLEDB connection.

       

      hope my question is somewhat clear.

      Thanks in advance

       

      Bram

       

      qv.jpg

        • Re: How to group by year in a chart with relative values?
          whiteline _

          Hi.

           

          You have to realize that the work with QV consists at least of two stages. First, you get the data from the datasources into some kind of dimensional data model, transforming the data if needed. Second you use that datamodel to build visual charts and so on, using dimensions and aggregation functions.

           

          Group by stays in the script, while in the front-end you aggregate the data on the fly.

          To write an expression that operates the data set that differs from the user selections you have to understand the power of set analysis syntax.

           

          If I get you right and you have some meaningful datamodel, I think this expression is the kind that you're looking for: =Sum(Registratienummer)/Sum({<Brand=>} total <Jaar> Registratienummer)

            • Re: How to group by year in a chart with relative values?
              Bram Van Helleputte

              Hey Aleksandr,

               

              Thanks for your reply. Although I do not wish to set and predefined values, I think it's more easy then the set analysis. The core of my problem is just a matter of relative representation. The problem only arises when I set the expression to relative. No one is interested in the market share  over 3 successive years (as is the case now). It's the % for each year that we need.

               

              I still can't figure this one out, I have the feeling I have to use the script but I dont have any load statements, only SQL select statements and the keywords don't seem to be 'active' (as in auto-suggestions)