I have a bar chart which shows a rolling 12 month period. In my master calendar, I create a sequential number sequence to identify combinations of month and year, called MonthYearID. So December 2009 might be 12345 and January 2010 might be 12346, etc.
When my dimension is MonthYearID, it works great. As I make selections on year and/or month, the twelve periods shown in the chart change so I'm always looking at twelve bars, with the last one being the selection. However, it looks a little ugly to have the bars at the bottom labeled 12345, 12346, 12347, etc.
When I switch my dimension to CalendarMonth, the data stays correct, but the order is wrong. So for instance, if I select October as a month and 2009 as a year, I see 12 bars representing November 2008 thrugh October 2009. But instead of starting with November and ending with October, it will start with January and end in December - which is prety confusing when the last two bars on the chart are earlier than the rest.
I have tried various sort expressions, but that is a part of QlikView I haven't explored much yet, and I can't figure out how to get the CalendarMonth dimension to dynamically sort based on the selection.
I have, and that gives me some very strange results. It doesn't seem to change the order at all, until I add a second expression. The second expression is the same as the first one, except I subtract an additional 12 from both MonthYearID references so I can get two bars per month for year over year comparisons. Then selecting a month sometimes just swaps two months. Other times it seems to put them in bizarre orders.
Yep, here's a stripped down QVW with my data and some charts.
As you can see, the top chart works fine, where I have one expression. I am able to sort by MonthYear (which is just descriptive text that corresponds to a MonthYearID) and selecting various months in the list box ensures the month selected is the last bar and they still remain in order. This is with MonthYear as my dimension.
But when I add a second expression in the bottom chart to get two 12 month rolling periods and compare them, I need to change to the dimension to CalendarMonth, or else the first 12 and second 12 don't get grouped properly (since they are from different years). So how do I get that bottom chart to sort in the correct month sequence with the last bar always being the selected month with CalendarMonth as a dimension?
In case of no selections, the month order should be July-June, as that's the 1-12 fiscal periods.
The issue with that suggestion is that it is not dynamic in the x axis. The chart's sorting of months should reflect which month has been selected so if the month July is selected the x axis shows in order;
July - Aug - Sep - Oct etc...
If the Month Nov is selected then the x axis should show
Nov - Dec - Jan - Feb - Mar etc.
I believe this should be able to be done using a sort on the date field within a period of 365 days but have yet to get it to work.
