topic Re: Dimensions in Ascending Order - How? in QlikView

Dimensions in Ascending Order - How?

dmohanty — Thu, 05 Mar 2015 19:50:20 GMT

Hi Team

I have a graph like this below where I am showing the Latest 3 Months (from a Set of 13 months)
I have enabled the Dimension Limit as 'Show First 3 Values'
Now I want to show the X axis in Ascending Order (Dec-14 Jan-15 Feb-15). HOW?

(My Sort option is shown in next image)

Changing the Expression Sort to Ascending is not helping as it displays - Jan-14 Feb-14 Mar-14

Re: Dimensions in Ascending Order - How?

sunny_talwar — Thu, 05 Mar 2015 19:54:13 GMT

Is your [Month/Year] field a date field or text? If it is a date field then you can do a numeric value sort by ascending order. If it is text than you can put this as your sort order in the expression:

=Match([Month/Year], 'Dec-14', 'Jan-15', 'Feb-15')

HTH

Best,

Re: Dimensions in Ascending Order - How?

Thu, 05 Mar 2015 19:55:56 GMT

If the dimension is in date format enable only the Text A-Z sort option. I think that works.

Re: Dimensions in Ascending Order - How?

dmohanty — Thu, 05 Mar 2015 20:01:35 GMT

HI Renato,

Enabling the Tex A-Z gives Jan-14 Feb-14 Mar-14.

This is not needed. Any more help please

Re: Dimensions in Ascending Order - How?

dmohanty — Thu, 05 Mar 2015 20:03:08 GMT

Hi Sunindia,

I can't do a hard coding here.... As it's kind of Rolling Month and will change next month.

Numeric Sort didn't helped.

Any SET ANALYSIS to restrict Latest 3 months from 13 months - to avoid Dimensional Limit ?

Re: Dimensions in Ascending Order - How?

sunny_talwar — Thu, 05 Mar 2015 20:13:30 GMT

You should be able to use set analysis, something like this maybe:

{<[Month/Year] = {'>=$(=Date(AddMonths(Max([Month/Year]), -3)))'}>}

Assuming [Month/Year] is a date field

Let me know if it doesn't work or if [Month/Year] is a different format.

Best,

Re: Dimensions in Ascending Order - How?

Anonymous — Thu, 05 Mar 2015 20:15:25 GMT

In the script you can sort the table that has the date field by Ascending and than check Load Order as your Sort option.

Re: Dimensions in Ascending Order - How?

MK_QSL — Thu, 05 Mar 2015 20:38:39 GMT

When you are creating your Month/Year field, create using Dual as below. This will allow you to sort the data by Numeric value..

Load

Dual(Date(MonthStart(Date),'MMM-YY'),Date(MonthStart(Date),'YYYYMM')) as [Month/Year]

Inline

[

Date, Sales

15/02/2015, 100

25/01/2015, 120

10/11/2014, 130

12/12/2014, 90

];

Re: Dimensions in Ascending Order - How?

simenkg — Thu, 05 Mar 2015 21:32:08 GMT

Sort by expression and use the expression:
=Max(Date)

Re: Dimensions in Ascending Order - How?

vishnus85 — Thu, 05 Mar 2015 21:44:54 GMT

I feel, the best way to deal with sorting of time is to create a numeric field corresponding to the every month and use it as the sort expression. That is, in your case, at the script side you can create a new field by name say 'MonthSort'

Dec-14 should have the MonthSort value as 201412

Jan-15 should have the MonthSort value as 201501

Feb-15 should have the MonthSort value as 201502

Later in the dimension sort tab, check to sort by expression and in the expression field just give MonthSort and make it ascending.