13 Replies Latest reply: Jan 12, 2017 3:36 PM by Petter Skjolden RSS

    Force showing all values on x-axis

    Stefan Rombouts

      Hi,

       

      I have loaded two tables into qlik sense and linked these: one with a calendar that contains (among others) a column for the date and the year/month combination, and one with orders, containing columns for 'shipping date', 'customer', 'product' and 'quantity'.

       

      Not every customer orders every product every month, and when I make a bar chart that groups sales by year/month on the x-axis, only the months in which there were sales are displayed (for example, if a certain customer only ordered a certain product in April and June of this year, and I filter down to this combination, it will remove all months except for these two from the x-axis, which does not look very good). I have tried switching on the 'show null values' option, but this does not work. I guess this is because in the source data these really aren't entries with null values, but rather no data exists at all.

       

      My question is: is there any way to force the chart to conistently show all months on the x-axis (or more ideally, only all months from the first to the last month in which there were sales), regardless of the customer and product I select, and regardless of whether any data exists for that month? In other words, all non existing data should simply show up as months with 0 sales.

       

      I have found some other threads on the forum asking for essentially the same thing (for example here), but unfortunately I have not been able to apply the solutions offered in these to my problem with any success.

       

      Thanks for the help!

        • Re: Force showing all values on x-axis
          Petter Skjolden

          You can make a so-called Master Calendar that contains all the dates from min to max date. You generate this in your load script and it is a standard thing that is taught in QlikView classes and the subject of most QlikView books. QlikView Components even have a best-practice "component"/script-function to generate one for you:

           

          https://community.qlik.com/thread/40043

           

          There is a lot of information on this community if you search for Master Calendar - for instance this intro from Michael Tarallo:

           

          Understanding the Master Calendar (video)

            • Re: Force showing all values on x-axis
              Stefan Rombouts

              Thank you very much for your help!

               

              I had actually already added a master calendar to the app. Using this I indeed get all values to show on the x-axis, including the ones on which there were no sales (which show up as 0).

               

              However, this unfortunately only seems to work if I do not filter the data in any way. The moment I start filtering the data (for example, selecting one particular product, or one particular customer), all dates with zero sales disappear from the x-axis again, despite having the "display null values" and "show zero values" buttons checked. So while this solution is a step in the right direction, It's not yet entirely what I'm looking for.

               

              The only solution that I have been able to come up with so far, is to add a 'dummy' measure that is always '0', and ignores any filter context. But this is not really ideal either...

            • Re: Force showing all values on x-axis
              Petter Skjolden

              This piece of code that I snipped from a community post (response from Marco Wedel) demonstrate the basics of it:

               

              tabCalendar:

              LOAD *,

                   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 Min(Date) as MinDate,

                   Max(Date) as MaxDate

              Resident tabSourceOfDate;

                • Re: Force showing all values on x-axis
                  Ron Dunn

                  Can you explain how this solves the problem?

                   

                  I asked a similar question (How to retain hierarchy view of null values?), but I think the answers to these questions are missing the point.

                   

                  As soon as a filtering selection is made, all null values are removed from the x-axis. This is particularly undesirable with a date dimension.

                    • Re: Force showing all values on x-axis
                      Petter Skjolden

                      It is a very important part of the solution. If you don't have values on your first dimension for every point that you possibly want to display there is no way of getting the the full x-axis. But it is not a full solution in itself. You have more than one way of solving the last part. You could either tweak some options or you could add values in your load script - either 0 values or some valid value copied from previous values or null values.

                       

                      It is hard for me at least to explain "theoretically" so a simple test app with test data is the quickest way of demonstrating what to do...

                        • Re: Force showing all values on x-axis
                          Ron Dunn

                          Hi Petter,

                           

                          I've attached the sample from which the screen shots in my referenced post were taken.

                           

                          Drill down to the day level in the bar chart, then press any segment in one of the donut charts. You'll see that only those days with values are shown in the bar chart.

                           

                          (Ignore the horizontal line, it was testing ideas to try and force days to appear)

                           

                          Ron.

                    • Re: Force showing all values on x-axis
                      danelle hee

                      Hi Stefan,

                       

                      This is extremely late but one of my coworkers found a workaround to this exact problem:

                       

                      In the set analysis of your measure (Sales) write:

                       

                      if(Sum({1} [2nd bar graph dimension]) > 0, Sum([Sales]),0)

                       

                      Now, when you make selections, all dates should remain displayed even if 0 products/ customers made an order during that month.

                       

                      Hope this helps.