8 Replies Latest reply: Jul 21, 2011 7:53 PM by John Witherspoon RSS

    How to have 2 fields in the X axis of a graph

      Hi!

       

      Im new to Qlikview, and have the Personal Edition to see if its the right product for us. One test we are carrying out is replicating data and graphs we have in Excel into Qlikview.

       

      Is it possible to have 2 fields in the X axis in a line graph? I have 3 fields, Day, Week of Year and Amount.

       

      Im looking to have a graph that has Day and WeekofYear on the X axis, represented as the following;

       

       

      Mon Tue Wed Thu Fri        Mon Tue Wed Thu Fri           Mon Tue Wed Thu Fri 

       

                Week 1                            Week 2                               Week3

       

       

      At the moment, when I add Day and WeekofYear in the dimensions, it puts Day in the X axis, and treats WeekofYear as a legend entry.

       

      Is there any way to achieve this? Its a very important measure for the company I am trying to create, and will probably be the a key factor on if we purchase the product.

       

      Thanks!

        • How to have 2 fields in the X axis of a graph

          Normally there is something called a Cyclic Group whcih is used in between days, months and years. But if you want to see both the dimensions at onetime, then you can create a dimension field with with a concatenation of day and week and use it as a dimension.

          • How to have 2 fields in the X axis of a graph
            Stefan Wühl

            Add a second expression and make it invisible should do the trick.

             

            Regards,

            Stefan

              • How to have 2 fields in the X axis of a graph

                I've tried both suggestions, and they dont work.

                 

                Swuehl, how will adding a second expression give the x axis a second layer?

                  • Re: How to have 2 fields in the X axis of a graph
                    Stefan Wühl

                    Hi,

                     

                    if you have only one expression, the second dimension will be used as label, with two or more expressions, you get something I think you intend to get.

                     

                    Please look at my attached image.

                     

                    I used two dimensions, week# and day of week.

                    One expression is count of something, one is 1 and made invisible.

                     

                    Regards,

                    Stefan

                     

                    Nachricht geändert durch swuehl

                      • How to have 2 fields in the X axis of a graph

                        That works with a bar chart, but I need a line graph.

                         

                        I've attached the line graph I've created in excel...

                         

                        untitled.JPG

                          • How to have 2 fields in the X axis of a graph
                            Stefan Wühl

                            Sorry, I missed your line chart requirement.

                             

                            I tried for a while and also searched in the forum, and couldn't get it to work exactely as you require.

                            Furthermore, it seems that QlikView has some issues with line charts / combo charts with more than one dimension, for example if I do a combo with week# and day of week as dimension and count something as expression as line and bar chart, the line look pretty strange.

                             

                            So I think a good solution is to use Mady mama's suggestion with a concatenated dimension field, I also use this quite often.

                             

                            Regards,

                            Stefan

                             

                            I think since Version 10, there is the possibility to use extension objects (like 3rd party sheet objects, extending the available graph objects). There might be an extension object which may fulfil the requirements, but haven't looked for that.

                              • Re: How to have 2 fields in the X axis of a graph
                                John Witherspoon

                                It's a very basic capability, but I don't think QlikView does it.  As mentioned, concatenating the day and week would work fine and is a typical solution.  The only thing is that it will repeat your week for every date, like 'week 27 Mon 2' 'week 27 Tue 3' and so on.  You can sort of work around this if it really bothers you.  I've attached an example with years and months as my "dimensions", and the year only displaying next to the first month of that year included in the chart, but you have personal edition so can't see it, so...

                                 

                                The script:

                                 

                                LOAD *
                                ,month(YearMonth) as Month
                                ,year(YearMonth) as Year
                                ;
                                LOAD
                                date(addmonths(makedate(2009,1,1),floor(rand()*24)),'YYYY MMM') as YearMonth
                                ,ceil(rand()*10) as Amount
                                AUTOGENERATE 100
                                ;

                                 

                                The line chart:

                                 

                                dimension=aggr(date(YearMonth,if(Month=min(total <Year> Month),'YYYY MMM','          MMM')),Year,YearMonth)
                                expression=sum(Amount)

                                 

                                It's possible that things are better in version 10 or 11.  I mostly use version 9.

                          • How to have 2 fields in the X axis of a graph

                            Hello,

                             

                            " create a dimension field with a concatenation of day and week and use it as a dimension" ----

                             

                            This should work for you as i had a similar requirement in the past. Even now when I have to show data for Year and Months, I dont use 2 dimensions, instead I created a new dimension in the script which is called YearMonth and then use it. however you need to do it in the master calendar or your Time periods(Calendar) Table.

                             

                            Thanks