Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to present a straight table with headlines as dimension and sum(amount) as expression. I want to show all values of the headlines even though there are no amounts associated with all headlines. I have ticked show all values under the dimension. So far so good. The problem is that I want to sort the order of the headlines in a specific way, according to another field, Sort, which is a numeric field with numeric values. So in the sort tab I have ticked sort by expression, and put the field Sort and ascending. However when I make a selection, for example a date, the sort order changes. I can still see all values of the headlines, but the ones without amount on top, and the ones with amount in the bottom. What can I do to keep the sort order even when I make selections?
I have also tried to use, Load Order original in the Sort tab. The field sort is not sorted in ascending or descending in the script, so I user order by Sort in the script, but that doesn’t do anything. Like this
Test:
NoConcatenate LOAD
*
Resident Testtmp
Order by Sort asc;
DROP Table Testtmp;
The field Sort doesn't get sorted in numeric ascending order. It still shows the values in random order.
Hi Hanna,
You will need to add a order field, in your case the function RecNo() will do
Test:
NoConcatenate LOAD
*,
RecNo() AS OrderNo
Resident Testtmp
Order by Sort asc;
DROP Table Testtmp;
Once the script is reloaded, you can sort by this field in the Expression textbox.
Hope that helps.
Miguel
Hi Hanna,
if i understand you correctly then you can use the Match function ...
got to the chart properties and select only sort by expression and ascending . in the expression statement use match function like
match(column,' value1', ' value2',...)
hope this will help.
Regards
Sal1
Hi, thanks for the replies.
Miguel, it both worked and didn't work. It did the sorting right, but when I put OrderNo in the straight table under expression in the sort tab, it still does the sorting in random order, placing 275 first instead of one. I'm not sure why.
In the Sort field I have several occurrences of the same value, i.e. the value 2345 can occur 5 times. But that shouldn't really matter?
Sal, there are too many values to use the match function.
/ Hanna
Hi Hanna,
There are some cases in charts where the sort may be different or not applicable, for example if you are using the TOTAL qualifier in some of your expressions. Check in the Sort tab of the chart properties and promote / demote the columns to get the priority you want.
If you are using the OrderNo as expression, uncheck all the other sort options, and leave only the expression ascending or descending.
Hope that helps.
Miguel
Ok, here's how I solved it. My resident load was loading from a table which in turn was loading from a qvd file. I simply did the sorting in the application that was "feeding" the qvd file, and that actually seems to have solved my problem!
Thanks for all help!
/ Hanna