5 Replies Latest reply: Apr 28, 2011 9:47 PM by S H RSS

    Automatic expression axis ranges are too high

      Problem:
      I've built a combo chart that has unexpectedly large values for the expression axes. The maximum value along the axes is much, much higher than the maximum value of the expressions. This results in the bars and line in the chart being compressed near the y-axis.

      Weird observation:
      If I select all of the data along the dimension axis (which shouldn't have any affect since I'm not excluding values), the expression axes automatically update to much lower values that one would expect.

      Dimension:
      WeekName(Date)

      Bar chart expression:
      Count( IF(Status='closed', ID) )

      Line expression:
      AVG( IF(Status='closed', Cost) )

        • Automatic expression axis ranges are too high
          John Witherspoon

          I sometimes see this happen. I've never really investigated the reason. My solution is to manually calculate a good maximum myself and use it as the static max. So we want to do something like this:

          max(aggr(count({<Status*={'closed'}>} ID ),weekname(Date)))
          max(aggr( avg({<Status*={'closed'}>} Cost),weekname(Date)))

          The problem with that is that you can't use calculated dimensions in aggr(). I suggest creating an actual field for the weekname(Date), using that as your dimension, and then doing this:

          Dimension = WeekName
          Expression 1 = count({<Status*={'closed'}>} ID)
          Static Max 1 = max(aggr(count({<Status*={'closed'}>} ID ),WeekName))
          Expression 2 = avg({<Status*={'closed'}>} Cost)
          Static Max 2 = max(aggr( avg({<Status*={'closed'}>} Cost),WeekName))

            • Automatic expression axis ranges are too high

              Thanks for your feedback, John. I gave that a whirl for average cost. After adding WeekName to my data set rather than using a calculated dimenion, I found that adding the static max changed the expression axis from 40,000 to 30,000. That, unfortunately, is still way to high. I verified that the static max calculation you gave me expected results, as a table of WeekName and the static max calculation give me expected results (the max among all weeks, by the way, is $888).

              Do you think this is a bug or is there some fundamental configuration issue that I'm not grasping here?

              • Automatic expression axis ranges are too high

                John, I stand corrected. Your suggestion was spot on.

                I found that that what I observed was caused by one record with an extraordinarily high cost (on the order of $500,000) but whose date was null. Since date was null for this record, it was not used in the calculation of average cost for the line in the chart. However, QlikView's automatic calculation of expression axis length takes into account all records, even those that have a null for the dimension used in the chart. I found that I could force QlikView to ignore records that had a null in the dimension by checking the 'Supress When Value is Null' checkbox in the Dimensions tab of the Chart Properties dialog box. Doing so caused the maximum value of the expression axis to change to a value that was expected, and the bars and line in the chart are no longer compressed toward the x-axis.

                This also explains why dragging my cursor along the dimension axis to select all dates caused the maximum value of the expression axis to change to a value that was expected (as shown in the screenshot in my original post). Doing so selected only records that had a date, and it was based on those records that QlikView automatically re-adjusted the expression axis.

                Thanks for your help!

              • Automatic expression axis ranges are too high

                The reason for that is QlikView does not show all the bars when there is too many bars to show and it is true because you have so many dates. QlikView axes scaling is correct.

                To resolve your problem is to go to Properties \ Presentation and check the check box labelled 'Show All Bars'. To make things slightly better, you may also choose to select 'Allow Thin Bars' as well.

                Regards,

                Dinesh.