topic Re: Sorting a calculated dimension in a bar chart in QlikView

Sorting a calculated dimension in a bar chart

Anonymous — Tue, 04 Oct 2016 19:47:20 GMT

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

Re: Sorting a calculated dimension in a bar chart

sunny_talwar — Tue, 04 Oct 2016 20:20:03 GMT

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

RangeSum(Year, Num(Month)*10000)

Re: Sorting a calculated dimension in a bar chart

maxgro — Tue, 04 Oct 2016 20:21:50 GMT

maybe sorting (Sort tab) only by this expression

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

Re: Sorting a calculated dimension in a bar chart

Anonymous — Tue, 04 Oct 2016 20:57:53 GMT

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.

Re: Sorting a calculated dimension in a bar chart

Anonymous — Tue, 04 Oct 2016 21:01:31 GMT

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...... .

Re: Sorting a calculated dimension in a bar chart

sunny_talwar — Tue, 04 Oct 2016 21:27:39 GMT

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)

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?

Re: Sorting a calculated dimension in a bar chart

sunny_talwar — Tue, 04 Oct 2016 21:38:03 GMT

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

QlikView Addict: Handling Nulls in QlikView