# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor II

## Sorting a calculated dimension in a bar chart

Hello Guys,

I am having issue while sorting a calculated dimension in a bar chart. I am using calculated dimension which produce yearly data point as well as monthly data points(only for the last year).I need to sort the data field firstly by year and then by month. I have tried using match function in expression in sort tab but it's not working for me.

The following calculated dimension I am using :

=if(Year <> vMaxYear,Year,Month)

So, in case of last year it will show data monthly wise otherwise yearly.

Please refer to below graph for output. So, I need to have data like this 2009, 2010, jan , feb, mar, apr...... dec.

Any help would be appreciated.

Thanks & Regards

Harsh

1 Solution

Accepted Solutions
MVP

May be try a sort expression like this with ascending sort:

RangeSum(Year, Num(Month)*10000)

6 Replies
MVP

May be try a sort expression like this with ascending sort:

RangeSum(Year, Num(Month)*10000)

MVP

maybe sorting (Sort tab) only by this expression

=if(Year <> vMaxYear,Year,Month*10000)

Contributor II
Author

Thanks Sunny for the quick reply. It worked like charm. I just have a little doubt how we are using rangesum for sorting purpose here? Normally, we use it for rolled up sum, avg etc... if you could explain it a bit that would be much help for better understanding.

Contributor II
Author

Thanks Massimo for immediate respone. your solution did work too but by multiplying the month with 10000 it is changing the data points values into 10000, 20000, 300000...... .

MVP

The only reason I used RangeSum is because there might be nulls which I thought might create problems. I could have just given you this:

Year + Num(Month) * 10000

But if either Year or Num(Month) * 10000 are null, the whole row will turn into null. Whereas,

RangeSum(Year, Num(Month)*10000)

will take care of nulls.

So, RangeSum is not just used for rolled up sum (not sure what that means), but it is also used to Sum two expressions where one can be null at times. Other longer alternatives can be like this

Alt(Year, 0) + Alt(Num(Month) * 10000, 0)

or

If(Len(Trim(Year)) = 0, 0, Year) + If(Len(Trim(Num(Month))) = 0, 0, Num(Month) * 10000)

All of these are safety nets and not necessarily needed. But why not use a simple RangeSum instead of + if it is more robust, right?

MVP

You can also read about some of what I explained here under the header 'Nulls in Basic Maths'

QlikView Addict: Handling Nulls in QlikView

Community Browser