Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Wrong order for dimensions in a pivot chart

Hi,

in a pivot chart I have year and month dimensions.

The month dimension is built in this way: pick(num(right(Month_Year,2)), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'). For the order (crescent) I have specified the same expression in the match(...) statement, but the order isn't rigthly executed when I select one or more years in a list box. I have also specified the order for Year dimension that precedes the month dimension.

Any suggests to me, please? Thanks

2 Replies
fosuzuki
Valued Contributor II

Wrong order for dimensions in a pivot chart

Hi,

I think you could create your month dimension use Dual values. For example, create january as dual('January', 1).

On the Sort tab, select numeric value and it will automatically use the numeric part of it.

Hope this helps you.

Regards,

Fernando

MVP
MVP

Re: Wrong order for dimensions in a pivot chart

Easiest is if you can settle for the three character abbreviations.  Then you can use real QlikView date fields, not numbers and text.  So if your Month_Year field looks like YYYYMM, do something like this in the script:

,date(date#(Month_Year,'YYYYMM')) as Month_Year
,month(date#(Month_Year,'YYYYMM')) as Month

If you want the full names, you could do the above AND override the abbreviations with the full names:

SET MonthNames='January;February;...etc...';

I think that would work, but I think it make the abbreviations inaccessible.

And yeah, as Fernando says, dual() would do the trick too.  I just tend to prefer using real dates and real date fields.  Once you start using numbers and text, it gets harder to do date manipulation.  Anyway, here's dual, I think:

,dual(date(date#(Month_Year,'YYYYMM'),'MMMM') // MMMM is full name
    ,month(date#(Month_Year,'YYYYMM')) as Month

Then sort numerically as he said.

Community Browser