14 Replies Latest reply: Feb 3, 2016 5:10 AM by Maarten Jorritsma RSS

    Variable X-axis

      I have a dataset that I want to graph, for instance 'number of occurances / month', where a month without occurances should still show up.

      I have created a master calendar for the date-field.

      I have a set of data ranging from 2013 to today (jan 2016).

      I have created variables for the current and previous year

       

      Now I'm running into some problems when I try to graph out my occurances per month, since I want to also show the months where no occurances happened;

       

      • When I set the x-axis to continuous, I have to use a date-value (so for instance '=Month(DateField)') to graph on the X-axis instead of the MonthYear field from my master calendar. In this graph I can't get the X-axis labelled correctly (since it labels dates, I can't get the label to perfectly match with the bars on my graph (example1)
      • When I use the MonthYear field from my master calendar, I can either;
        • Show all values; which shows the values from 2013-2016 (example2)
        • Not show all values; but now the months without occurances are hidden (example3)

       

      I would like a graph that looks like example1 (but works and labels with the MonthYear from my mastercalendar), or like example2, but then I would love to be able to set the graph to only show MonthYears that fall into the 2 variables (varCurrentYear and varPreviousYear) that I've set up.

       

      What's the best way to approach this?

       

      example1:

      QV1.jpg

      example2:

      QV3.jpg

      example3:

      QV2.jpg

        • Re: Variable X-axis
          Oleg Troyansky

          Example 1 seems to be impossible because your bars overlap multiple months - QlikView doesn't do that.

          Example 2 is easy to achieve if you declare your Axis as Continuous and format your months accordingly.

           

          In order to limit your date range to the two selected Year, I'd suggest adding that as Set Analysis filters, it works better than doing the same in a Calculated Dimension.

           

          Cheers,

          Oleg Troyansky

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

            • Re: Variable X-axis

              Exampe1 is possible, since i have managed to create it

              -the used dimention is "month(DateField)", so it makes a single colum for every month. I just can't get it display a single label per month.

               

              I gather my data with the following set anyalsis formula;

               

              count({$<Type={'A','B'},[Date]={'*'},[YearDate]={$(varPreviousYear),$(varCurrentYear)}>}RegNr)

               

              In Example 2 and 3 I already used the MonthYear as declared in my set analysis formula, but I can't set the x-asis to 'continous' when I use the MonthYear from my mastercalendar as a dimention.

               

              I really can't figure out how I can label the 'continuous' x-asis with month labels, since it HAS to be a date field (and not a MonthYear field) for me to be able to set the axis to continuous in the first place.

                • Re: Variable X-axis
                  Oleg Troyansky

                  Continuous x-axis can be formatted in the Number tab. Format your date as a Month-Year:

                   

                  MMM-YYYY or MMM-YY

                   

                  cheers,

                  Oleg Troyansky

                    • Re: Variable X-axis

                      I've tried that, but then I get multiple labels for every month.

                       

                      I've tried setting a static step for the continuous axis. If I set it to '30' I do get a label for every month, but then they're not correctly aligned with the  bars on the graphs. Is there any way to get these labels to sit correctly under the bars?

                       

                      Screenshot of the graph with the 'static step' on the axis set to '30';

                       

                      QV4.jpg

                        • Re: Variable X-axis
                          Oleg Troyansky

                          So, if you are showing monthly bars, why does your dimension HAVE to be a date and not a month, again? I am afraid that you are creating your obstacle artificially...

                            • Re: Variable X-axis

                              That's exactly the problem I'm running into.

                               

                              I want to show the monthly bars for the years in my selection (so let's say I have data from 2013 upto today, and only want to show 2015), and I want the months within that selection that don't have any data to show up.

                               

                              -if I don't use 'show all values' on the dimention the graph will only show the months that actually have data (example 3 from my original post)

                              -with the 'show all values' on the dimention it will also show 2013, 2014 and 2016 on the axis (example 2 from my original post)

                               

                              When I use the 'continuous' option on the Axis settings tab, I have trouble aligning the labels since I assume "in the background" it's still an axis with a point for all dates, not just for the MonthYear labels I have (example 1 from my orignal post, or the graph from my previous post)

                               

                              Unless I'm missing something completely obvious here (which is a valid possibility).

                                • Re: Variable X-axis
                                  Oleg Troyansky

                                  Hi Maarten,

                                   

                                  I admin, I'm having a hard time understanding your requirement... Perhaps a small example QVW could help visualize the problem and offer some alternatives... Are you saying that you keep the dimension as Date because you want to have daily bars, but only label them with the Month name?

                                   

                                  It sounded to me at first that you need to plot the number of occurrences per month, and that means that the bars should be monthly. In this case, Month, or MonthStart(Date) should be your Dimension, not Date.

                                   

                                  If you post an example, I could look into it.

                                   

                                  cheers,

                                   

                                  Oleg Troyansky

                              • Re: Variable X-axis
                                Gysbert Wassenaar

                                Can you post a small qlikview document that illustrates the problem?

                              • Re: Variable X-axis
                                Peter Cammaert

                                On the other hand, a continuous axis will in theory produce all date values (that's why you get multiple identical months as representations of some of the date values), while a month-year value is by design a discrete value when based on a real date value. Serious conflict here.

                          • Re: Variable X-axis
                            Marco Wedel

                            Hi,

                             

                            maybe one solution might be:

                             

                            QlikCommunity_Thread_203538_Pic2.JPG

                             

                            QlikCommunity_Thread_203538_Pic3.JPG

                             

                            QlikCommunity_Thread_203538_Pic1.JPG

                             

                             

                            table1:
                            LOAD Date(MakeDate(2013)+Round(Rand()*(Today()-MakeDate(2013)))) as Date,
                                 Ceil(Rand()*100) as Value
                            AutoGenerate 15;
                            
                            tabCalendar:  
                            LOAD *,
                                 -InYear(Date, Today(),0) as InCurrentYear,
                                 -InYear(Date, Today(),-1) as InPreviousYear,
                                 Day(Date) as Day,  
                                 WeekDay(Date) as WeekDay,  
                                 Week(Date) as Week,  
                                 WeekName(Date) as WeekName,  
                                 Month(Date) as Month,  
                                 MonthName(Date) as MonthName,  
                                 Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,  
                                 QuarterName(Date) as QuarterName,  
                                 Year(Date) as Year,  
                                 WeekYear(Date) as WeekYear;    
                            LOAD Date(MinDate+IterNo()-1) as Date  
                            While MinDate+IterNo()-1 <= MaxDate;  
                            LOAD MakeDate(2013) as MinDate,  
                                 Today() as MaxDate  
                            AutoGenerate 1;
                            
                            

                             

                            hope this helps

                             

                            regards

                             

                            Marco

                              • Re: Variable X-axis

                                The graphs you show do exactly what I want them to do.

                                 

                                I'm trying to reproduce them in my own model, but I can't get the months without data points to show in my graphs, even when I'm almost 100% sure I have all of the settings in the graph set exactly them same as yours.

                                 

                                I'm starting to think that the months without data points need to have a value of zero for them to show up, is this what you're doing with the '....value*(InCurentYear+InPreviousYear)' clause in the set analysis formula?

                                 

                                When I get the time I'll try to reproduce the problem I have in a small QlikView example that I'll upload. That might make troubleshooting this easier.

                                To be continued when work permits.

                                 

                                EDIT:

                                 

                                I found out the problem lies in the fact that I'm using more set analysis selections to grab the data I want to graph.

                                 

                                If I select a couple of points (dates) from the set you provided in your exampe I get the same graphs as I'm getting in my own model; only the months for the data points selected will show on the bar graph; screenshot from the example you included.

                                 

                                QV5.jpg

                                So I'm guessing this method doesn't work since I'm making additional selections in my set analysis?

                                  • Re: Variable X-axis
                                    Peter Cammaert

                                    In set analysis, this is the same effect as you get when you simply add a listbox (e.g. Customers - see my remark below) that refers to another dimension field connected to the Facts table. Selections in such a field (or explicitly reducing your set like you are doing) will further reduce the number of possible months. After which you get a "broken" X-axis.

                                     

                                    There is one solution that might work. The Continuous option in your graph will be our savior if you can somehow escape from a date-based X-axis (too many identical labels and bar positions that are out of line). Try this:

                                     

                                    In your calendar LOAD, create an additional dual field (call it for example MonthYearSeq) that combines your preferred MonthYear formatting with a sequential integer that numbers each month from the first on. If you start for example on 1/1/2012, then jan-2012 will have nr 1, jan-2013 will have nr 13 and so on. The value range isn't particularly important, but the succession is. An example:

                                     

                                    :

                                    dual( date(DateFiueld, 'MMM-YYYY',

                                          (Year(DateField)-vFirstYear)*12+Month(DateField)) AS MonthYearSeq,

                                    :

                                     

                                    IMHO, if you use this field as dimension in a continous X-axis, you will get the months without data in addition to making the timeline immune to other selections. Only one remaining problem: formatting the X-axis labels. (sorry, cannot test this out, no Desktop at the moment)

                                     

                                    Another solution that has none of the disadvantages consists of adding so called zero-records to your facts table. They won't show up anywhere, and if you disable "Suppress zero values" in a standard chart, you will get a continuous timeline without any tricks. Be careful to create zero-records for all missing selection field/set analysis combinations, or your selections may again reduce the calendar...

                                     

                                    Best,

                                     

                                    Peter

                                      • Re: Variable X-axis

                                        Thanks, I was afraid that this was the case.

                                         

                                        I'll tinker around with with the two suggestions you mentioned. I'll update here if I find that one works well.

                                         

                                        It would be so useful to have the option to input values like 'week' or 'month' in the static step box for the continuous x-axis...

                                    • Re: Variable X-axis
                                      Peter Cammaert

                                      This works like a charm because every selection passes through the calendar. However, I suspect that we'll be in trouble again when you add another dimension (like for example Customer) to the Facts table.

                                       

                                      Can we resolve this by creating a huge calendar table that JOINS every other selection field value to every calendar date?