Vertical Column chart: Can't order on Date when using a cross tab
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:
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.
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.
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.
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?
The same question
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:
I hope this suggestion helps you meet your reporting requirements, Please let me know what you think of it.
regards,
David
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:
I hope this suggestion helps you meet your reporting requirements, Please let me know what you think of it.
regards,
David
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?
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?
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
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
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
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
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
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
Replies have been locked on this page!