Vertical Column chart: Can't order on Date when using a cross tab

Rutger Tjallema shared this question 2 years ago
Answered

Since updating to version 7.3 build 20170929, we are missing a sorting functionality in the Vertical column charts for cross tabs.

For a cross tab we are using a vertical column chart to compare the metric (no SQLs) of different Suppliers over time (months). For this we use a cross tab. For visualization we used the Vertical column chart. This resulted in one bar per Supplier per month with the months ordered from oldest to newest.

Cross tab setup:

1de217270be9a395ac20a2464e8171a3

Since updating, the months are ordered alphabetically, unless we turn on Time Series. Both options result in a unreadable graph.

Time series off (picture below). We do have one bar per supplier per month and they are next to each other. That is as desired. Unfortunately the order of the months is not chronological. So this is no option.

2ed4ece1e90f110a4ed7143434883b1f


Time series on (picture below): the months are chronological, that is good. But the bars are overlapping. So Time series also drop out as option.

f6c3b81b70c696cb1ecb844ec52802b1


Looking further into the sort options of the axis (Time series turned off). There is an third option: table. This makes it possible to display the graph as desired, provided that the date table is sorted old to new. But we would like to have the table sorted new to old, but this also swaps the order of the months in the graph.

Now, I was ready to accept this third option and order the table old to new.


But, when I look into reports with the same graph, but no cross tab, we do have the option to sort on Date. See pictures below.

d825c424b9fae137e006b659cb44c09bd4a47160f0399c5a224a1e72014447f3


So, I'm wondering whether the missing of this option in a cross tab setup is a bug or that is purposely removed in version 7.3. And, can I expect it to be different in the new version?

Comments (5)

photo
1

Hi Rutger,

all of your findings are expected behaviour:

1) if a date is not Time Series then it will show in alphabetical order

2) bars can overlap in a Time Series chart (because the points in time have to be discrete on the Horizontal Axis)

3) Crosstab reports don't offer the Sort By Date option whereas non-crosstab reports do (and this hasn't changed in 7.4)


I think a good solution for you in this situation is to use the Combination Chart called Overlay, because then you will have the months in chronological order, and there won't be chart points hidden behind one another. Here is an example:


/PSDRBingAAAMC9pQL0C5m4Aq5jJPozAAAAAElFTkSuQmCCAA==


I hope this suggestion helps you meet your reporting requirements, Please let me know what you think of it.


regards,

David

photo
1

Hi Dave,


Thanks for your reply! Unfortunately, the overlay chart will not do, as the number of Suppliers changes depending on the filters. It can be 2, but also 20. For now I 'solved' it by ordering on the 'Table' and order the data table on date ascending.

I'm quite surprised that you say that the sorting options didn't change. Please note that we upgraded from 7.2 to 7.3. Also other reports that use a cross tab are affected. See examples below. In version 7.2 we had them ordered on the date, after updating to 7.3 the ordering is set to Alphabetically.

Are you sure that this didn't change between the two versions? Were we 'just lucky' that it worked before?


7db6a8266d15229f69d8783af5efbe49

eb8a871b4ebbaa0fafa2ce934ef946bc

photo
1

Hi Rutger,

I have tested this in 7.2 and I found that if I have a cross tab report with a Vertical Bar chart, and the date on the Horizontal Axis has "Time Series" turned off, then the order is alphabetical and not chronological. I have attached a video of my test.

Please let me know if there is a misunderstanding and I have done something differently than you in my test.

Also please tell me which build of 7.2 you were using before you upgraded to 7.3, you never know - maybe things were different in that build.


regards.

David

photo
1

Hi Rutger,

I'm cleaning up my work list and I noticed this ticket is still in the "Awaiting Reply" mode, so I'm just wondering how you got on with it?

regards,

David

photo
1

I've created an idea to change this default behaviour here, if you'd like to support it Rutger, as I agree this default behaviour doesn't really make sense (at least, to me). cheers