Sorting a visualisation

[BigChris]

Ok - this might be a really simple question, but I just don't seem to be able find the answer. I've got a visualisation that displays the number of lines sold per day of the week. I can calculate the day of the week using _day_of_week, which obviously brings back an integer from 1 to 7. I then wrap a case statement round that to give me Mon to Sun. All very simple so far.

What I want to do is display Mon - Sun in the axis, but it produces Fri, Mon, Sat, Sun, Thurs etc. i.e. in alphabetical sequence.

What I want to do is display the day name, but sort but the day number...is that possible?

[ThatKingslayer]

I think you might need two data items in your query to accomplish this. For the sake of example, let's call them Dayand DayNumber.

The data item Dayyou already have which is the results of your _day_of_week wrapped in a case statement to get Mon to Sun. The other (DayNumber) is a data item which is just the results of _day_of_week without the case statement. In your visualization, you will display Day but in your sort, you will sort by DayNumber.

Can you see if that gets you where you want to be?

[BigChris]

Hi - thanks for the reply. I should have put a little more meat on the bone :)

I've got two query data items:
DayNumber

Code Select Expand

_day_of_week([DateCreated],1)
DayName

Code Select Expand


case _day_of_week([DateCreated],1)
  when 1 then 'Mon'
  when 2 then 'Tues'
    etc.
  else null
end

I can bring either data item into my visualisation ( CA 11.1.3 by the way ), and I can sort by that item...but I can't bring DayName in and sort by DayNumber.

In C10 I was able to do that without any problems, but I just don't seem to be able to in C11. I'm assuming that I'm missing something obvious...

TIA

[MFGF]

Hi - thanks for the reply. I should have put a little more meat on the bone :)

I've got two query data items:
DayNumber

Code Select Expand

_day_of_week([DateCreated],1)
DayName

Code Select Expand


case _day_of_week([DateCreated],1)
  when 1 then 'Mon'
  when 2 then 'Tues'
    etc.
  else null
end

I can bring either data item into my visualisation ( CA 11.1.3 by the way ), and I can sort by that item...but I can't bring DayName in and sort by DayNumber.

In C10 I was able to do that without any problems, but I just don't seem to be able to in C11. I'm assuming that I'm missing something obvious...

TIA

Hi Chris,

Try this. Go to your query, and select the DayNumber item. In properties, set the Pre-Sort property to Sort Ascending.
Go back to your page, and drag the Daynumber item into the Extra Data slot for the visualization.

Does that work?

MF.