Help with chart sorting problem

[#1]

I am having problems with a chart not sorting correctly when I group the dates into six month segments.

My bar chart is just a simple count of activity split out into the various months (ranging from Jun 2008 to Dec 2013). The users require an option to view the data split by individual month (Jun 08, Jul 08, Aug 08, etc) or by six month periods (Jan-Jun 08, Jul-Dec 08, Jan-Jul 09, etc). I have used a variable to create this option, and have incorporated it into the dimension of my bar chart like so:

=IF(vTime='Six',

IF(Month([Date])<=6,'Jan-Jun','Jul-Dec')&' '&Year([Date]),

IF(vTime='One',

Date(MakeDate(Year([Date]),Month([Date]),1),'MMM-YY'),

null()))

The Dimension above is named "Month", and is the only dimension in the chart. On the sort tab, Month is sorted by Numeric Value Ascending.

When the axis is single months, the chart sorts correctly (as shown below):

But when the axis becomes six month periods, the bar for Jan-Jun 2008 moves towards the middle and out of sync with the rest of the bars. All other bars are in the correct order, apart from this one (see below):

I have tried copying the dimension expression into the sort expression to get it to work, but no success. I have also manipulated the dimension formula in the sort bar to convert Jan-Jun 2008 to 2008.1 and Jul-Dec 2008 to 2008.2, and that has not worked.

I am hoping somebody on the community can help with this, because I have tried the most logical things and still this small bar is in the wrong place!

There is nothing I can think of

[Nicole-Smith · Accepted solution]

Try:

=IF(vTime='Six',

IF(Month([Date])<=6,dual('Jan-Jun'&' '&Year([Date]),Year([Date])),dual('Jul-Dec'&' '&Year([Date]),Year([Date])+.5)),

IF(vTime='One',

Date(MakeDate(Year([Date]),Month([Date]),1),'MMM-YY'),

null()))

And sort by numeric value.

[swuehl]

I think you can prepare the two dimensions in your script and then just switch dimensions using your variable, but it should also work as is using a dual value and a numeric sort option:

=IF(vTime='Six',

IF(Month([Date])<=6,

dual('Jan-Jun '&Year([Date]),yearstart([Date]) ),

dual('Jul-Dec '&Year([Date]),yearstart([Date],0,7) ),

IF(vTime='One',

Date(MakeDate(Year([Date]),Month([Date]),1),'MMM-YY'),

null()))

[#4]

Thank you Nicole, that has worked a treat. Not entirely sure I understand why "Dual" works and "Num" doesn't, but at least the data is correct now

[Nicole-Smith]

When you're using dates that look like Jul-Dec 2008, QlikView thinks it is text.  We use the dual to assign a number to the text so that QlikView will treat it like a number.  The second portion of your if statement that deals with a date, casts the value as a date, which QlikView already treats like a dual (text with a numeric value behind it).