Pivot Sorting Help

[#1]

I'm currently trying to recreate a report built in another product. It's kind of a funny looking chart, but I should be able to build it using a QlikView pivot.

I'm having a problem sorting a calculated dimension. The calculated dimension goes across the top and it's the day of the month on one line and the weekday name (2 letter abbreviation) on the second line.

I want to sort the dimensions alphabetically and then sort the calculated dimension by date. The sorting on the calculated dimension seems completely random. The actual report could have multiple months of data starting on any day of the month, so simply sorting by day of the month will not work. I tried ordering in my load, but that didn't seem to help.

I've attached a simplified sample.

[#2]

I am not sure, I understand the problem or not. However, I tried to sort the attached file on day level by applying Sort By "Text" rather then "Expression" and it works well.

[#3 · Accepted solution]

After adding a second month of data and playing with the sort options unsucessfully, I checked the reference manual and it had the following about the expression sort option " Sorts the values according to the expression entered into the text edit box below this sort option. This option is not available for calculated dimensions."

The only way i was able to get the sorting to work correctly was to add USE_DATE to the dimension (you'll need the month and day) and sorting on that dimension. I set it to "always expanded" so it would display both the calculated dimension and the date. Unfortunately, this doesn't match your desired presentation ...

[#4]

I should have picked better dates for my sample. Text sorting won't work, because I can have more than two months of data on one chart. If I were to add 6/30 records to my chart, they would appear at the end, instead of the beginning.

dscanlon, thanks for pointing that out. I started thinking the Expression sort was completely broken. I was just doing it wrong.

Yesterday, I had a little success creating a counter in my load and then adding that as a dimension. Unfortunately, that didn't look so good. Using the full date as a dimension would be a good solution. I think I'm going to put my calculated dimension into my load script. Then I should be able to use Expression sort, since it will no longer be a calculated dimension.

Thank you!

[Anonymous]

Is it necessary to create the dimension as a calculated dimension. The formatting, at least in the attached example, can easily be done on the script side:

Load
*,
USE_DAY & CHR(13) & CHR(10) & USE_DOW as Dimension2;
LOAD * INLINE [
LOCALE,USE_DATE,SALES,USE_DAY,USE_DOW
BIRMINGHAM,7/1/2009,0,1,WE
BRUSSELS,7/2/2009,4,2,TH
BIRMINGHAM,7/2/2009,5,2,TH

etc...

Here I've added a preceding load to the inline load that creates the dimension as a field with the name Dimension2. This can in turn be used as the dimension in the chart and sorted any way you like since it's not being created on the fly.

//jsn

[#6]

I ended up doing something similar in order to get away from the calculated dimension. In this case and in my actual application, building the dimension in the script works pretty well.

[#7]

I know this thread is old news but I was looking for a solution today and came across this thread. I may have found a work around for this issue which I stumbled across quite by chance.

I calculated a dimension based on rank taking field 'item' if it was in the top 20 or null if not. As you would expect, I was unable to sort this. I then created a bar chart, which instead of having the calculated dimension, used 'item' and was restricted in the number of values displayed by the Max Vis Number option in the presentation tab. I then sorted this value by Y axis. When I looked back at my calculated dimension, based on 'item', it was sorted in the same way as the plain 'item' dimension!

Incredible. Let me know if it works for you too.