8 Replies Latest reply: May 21, 2012 5:18 PM by Adarsh Gupta RSS

    Multiple expressions in same chart

      I want to create a chart that has 2 expressions and 2 dimensions. I need 1 of the expressions to calculate for both dimensions and other expression to only consider 1 dimension.

       

      Essentially I am trying to create a chart that shows Market Share (measure 1) of a product for each territory (dimesion 1) over Time (dimeison 2), and I want to show national market share (measure 2) over time. Since its national market share, it should not consider any territory. When I put both expressions in the same chart, the X axis shows both the dimensions instead of just showing the time dimension.

       

      I've attached the sample that I am working on. You'll see 2 charts, I want to be able to merge those into 1.

       

      Thanks for your help!

        • Multiple expressions in same chart
          Jason Michaelides

          To exclude a dimension from your expression calculations you need to use the TOTAL qualifier.  For example, if you have a chart with Region and ProductCategory and an expression Sum(Sales), to exclude the ProductCategory dimension you would use Sum(TOTAL <Region> Sales)

           

          Hope this helps,

           

          Jason

            • Multiple expressions in same chart

              Thanks Jason! I tried that and few other options but I am not excluding just the data of a dimension but I need this to think that I dont even have a another dimension attribute on the dimension tab.

               

              When I do the TOTAL, the X-axis shows both of the dimensions and the numbers do not make sense with that view.

               

              Thanks!

                • Re: Multiple expressions in same chart
                  Jason Michaelides

                  Sorry mate - only thing I can think of is to overlay two charts with a static y-axis max value. See attached.

                  • Re: Multiple expressions in same chart
                    Jonathan Dienst

                    Hi

                     

                    I think with a line chart you can show 1 line with two dimensions (like your first chart), OR you can show multiple expressions. AFAIK you cannot have both.

                    If the number of territories is fixed and known, you could write one expression per territory and then your second expression.

                     

                    The other option is to overlay two graphs. This can work but can cause problems when selections are made the the two overlaid charts get out of alignment. Overriding or limiting selections can reduce this problem.

                     

                    Regards

                    Jonathan

                      • Re: Multiple expressions in same chart
                        Jonathan Dienst

                        Hi

                         

                        Another suggestion came to mind - create a calculated dimension or an island dimension with the territory name and the final expression name. Then in your expression, write something like (call the dimension TerrDim for sake of example):

                         

                        =If(TerrDim <> 'National MS%', .... your MS% expression here ...., .... your National MS% expression here)

                         

                        Load the final expression name last and sort by load order.

                         

                        Hope that helps

                        Jonathan

                          • Re: Multiple expressions in same chart

                            Can you please eloborate the part about creating calculated dimension? I am not sure I could follow that!

                             

                            Thanks!

                              • Re: Multiple expressions in same chart
                                Jonathan Dienst

                                Hi

                                 

                                Actually I prefer the approach with an logical island dimension - I think it will probably perform better than the calculated dimension and also simpifies the expression. You can create this in script from the data already loaded:

                                 

                                 

                                 

                                Data:
                                LOAD Products, 
                                     Time, 
                                     Territory, 
                                     TRX
                                FROM
                                [C:\Temp\Qlikview Apps\Test NMS.xls]
                                (biff, embedded labels);
                                
                                Island:
                                LOAD DISTINCT Territory As TerrDim
                                Resident Data
                                Order By Territory;
                                
                                Concatenate (Island)
                                LOAD 'National MS%' As TerrDim
                                Autogenerate 1;
                                
                                

                                 

                                Now use TerrDim as the second dimension in the chart and the expression that I described earlier.

                                 

                                Regards

                                Jonathan

                                  • Re: Multiple expressions in same chart

                                    QV Image.bmp Hi Jonathan,

                                     

                                    I tried your suggestion but i can see either national market share or market share by territory but i cant see both at the same time.I have attached a screen shot which shows how i want to see my final result.dotted line is the national MS%.But i made that using 2 charts by overlapping together but i want to get the same result by using one chart.

                                     

                                    Any suggestions?

                                     

                                    Thanks in advance