Fusion Chart X-axis Ordering

[rackem]

I am working on a Fusion Chart here to display ratings (y-axis) versus my record id (x-axis).

http://www.mypoolstats.com/admin/performance-chart

My question is how change the order of the X-axis. It seems to be taking the ordering of my List which is by record id descending. However for the graph I want it to display sorted by record id ascending so it shows how a persons rating changes over "time" from left-to-right.

I have gone through all the Fabrik settings and also the Fusion Chart forum. It seems that this would need to be toggled on the Fabrik side?

 

[rackem]

To clarify, I am trying to chart the last 20 records in my table.

So it doesn't work to just change the List's order, as then I just get the first 20 records.

 

[rackem]

*friendly bump*

Just to clarify, I am looking to display the last 20 records but have them displayed in ascending order. In other words, the query for the viz needs to be something like below, where "id" is my x-axis value and "rating" is my y-axis value.

SELECT (*) FROM
(SELECT id, rating FROM my_table ORDER BY id DESC LIMIT 20)
ORDER BY id ASC

Is this possible?

 

[rob]

Hi
I think you can alter the url that loads the visualization see here http://fabrikar.com/forums/index.php?wiki/sorting-data/#ordering-with-the-querystring
Otherwise copy the list, and set the ordering there. Then assign the copied list to the visualization

 

[rackem]

Hi Rob, yes I have tried both of those approaches. It doesn't help to just change the List's order, as then I just get the first 20 records.

I can get the last 20 records with the existing functionality, I just need a way to invert the order afterwards.

 

[rackem]

*friendly bump*

 

[rob]

perhaps you need to create a mySQL view of your data then - then build the list & viz from the view.
MySQL views are fairly simple to make, create your query in phpmyadmin, ensuring that your select statement contains the field names you want to include in the view (so not simply "select * from", but "select foo, bar from")
Once you are happy with the query you can press the 'create view' button in phpmyadmin, which takes you to a form where you supply the view name. Once created you can treat the view pretty much the same as a db table with respect to Fabrik except that you can't insert records directly into the view (but records inserted into the table the view was created from will be shown in the view's data)

 

[rob]

perhaps you need to create a mySQL view of your data then - then build the list & viz from the view.
MySQL views are fairly simple to make, create your query in phpmyadmin, ensuring that your select statement contains the field names you want to include in the view (so not simply "select * from", but "select foo, bar from")
Once you are happy with the query you can press the 'create view' button in phpmyadmin, which takes you to a form where you supply the view name. Once created you can treat the view pretty much the same as a db table with respect to Fabrik except that you can't insert records directly into the view (but records inserted into the table the view was created from will be shown in the view's data)

 

[rackem]

Hmm, I don't think a MySQL View will work in this case. My list contains rating information for many different players with several types each. So a MySQL View would need to display the last X number of records for N configurations (100's or more) which would be a very expensive query from what I've seen looking into this.

Here is more info about my current setup. You can see the Viz by clicking on the "graph" link in the rating column: http://www.mypoolstats.com/performance-ratings

I use a URL querystring, which is generated dynamically from a calc element, to filter the appropriate records for the Viz and display them in ASC order which is basically chronological order from left-to-right. That part works fine however as the number of data points increases, the Viz gets harder to read and slows down which is why I wanted to use the Limit option - to only show the last X number of points.

The issue is that when the limit option is used, the resulting records depend on the order direction you have set, i.e. if set ASC you get the first X records, if set DESC you get the last X records. There is no way to get the last X records in ASC order. So I was wondering if there was an option in the Viz to set the X-axis ordering ASC or DESC as needed instead of just using the list's order (or the querystring order in my case).

 

[rackem]

As a workaround, I have just set the LIMIT option above where this would be an issue during typical use. I didn't want to leave it blank in case a user tried to view all the data points (25k and counting) in the list.

I have added this option as a feature request here: http://fabrik.uservoice.com/forums/...s/5160258-toggle-fusion-chart-x-axis-ordering