4 Replies Latest reply: Jan 3, 2011 8:50 PM by John Witherspoon RSS

    Chart - Two fields on the X Axis?

      Hi All!

      Scenario: I want to chart SUM(Sales) by a grouping of Year and Quarter.

      If I add fields "Year" and "Quarter" as Dimensions #1 and #2 in the Chart wizard, Year becomes my label on the X-axis and Quarter becomes a series on the chart. I want to instead GROUP by Year, then by Month on the X-Axis, ala:

      error loading image

      I was able to accomplish this in QlikView by creating a Calculated Dimension which concantentates Year & Quarter, but this seems like an overly complex way to approach such a simple situation. Can someone someone point me to what I'm missing in the wizard? There must be an easier way.

      Thanks!

        • Chart - Two fields on the X Axis?
          Peter Rieper

          Am afraid, but this possibility does not exist, but instead of using calculated dimension you may use QUARTERNAME()-function.

          HTH
          Peter

          • Chart - Two fields on the X Axis?

            Typically you would create that data at load time and not have to mess with a calculated dimension. But a calculated dimension works too.

              • Chart - Two fields on the X Axis?
                Peter Rieper

                Thanks Cisco_Kid for the comment.
                Sorry for not having mentioned that.
                Think that it it in general better to have dimensions precalculated in the script, especially when working with dates.

                HTH
                Peter

                  • Chart - Two fields on the X Axis?
                    John Witherspoon

                    I do think it's a bit of a mistake for QlikView to not support multiple X-axis dimensions, but it doesn't (unless as a new feature in version 10). As said, the likely answer in this case is to create a YearQuarter field in your script. I would avoid calculated dimensions whenever possible, and it's certainly possible here. I'd generally make that field look something like 2011 Q1, and arrange it vertically or angled in the chart. But if you really want to make it display exactly as shown, you could format it with the carriage returns and all.

                    You probably don't want to just concatenate the fields, though. I'd personally want a date underneath everything, allowing you to use date functions on it and easily sort regardless of display format, probably the first day of the quarter. So let's say you have a Date field, and you're establishing the quarter from the date. You might want something like this in your script:

                    ,ceil(month(Date)/3) as Quarter
                    ,dual(ceil(month(Date)/3) & '

                    ' & year(date),quarterstart(Date)) as YearQuarter