Ordering data aggregated by month in ascending order in a table transformer (and chart)

[Green_ Desmond]

I cannot get a table transformer to order data grouped by month (and year).

I can group my data by month and year, as follows:

I did this using the following SQL

SELECT FORMATDATE(T1.'Date', "MM yy") As Month, SUM(T1.'Number') As Number FROM T1 GROUP BY FORMATDATE(T1.'Date', "MM yy")

The table transformer settings for date are:

When I use a chart to display the output of the table transformer, I get this:

The data is obviously in the wrong order! How do I best change this so I can show oldest dates first in the chart? If it makes it easier, I can remove the year in fact I would like to!

I previously had this working in a pivot table, but something changed and the pivot table stopped aggregating data by month. In any case, I tend to prefer the flexibility of table transformers and want to solve this problem with a table transformer.

[Stiltsoft support · Accepted solution]

Hi @Green_ Desmond ,

If we are talking about the Table Filter, Charts & Spreadsheets for Confluence app and its Pivot Table macro, then you may check our documentation for reference:

• How to set a corresponding date format inside your Pivot Table macro https://docs.stiltsoft.com/tfac/dc-server/managing-pivot-table-options-42239653.html#Managingpivottableoptions-Setthedateformat
• How to group your data by time periods inside your Pivot Table https://docs.stiltsoft.com/tfac/dc-server/aggregation-by-date-period-and-number-range-42239654.html#Aggregationbydateperiodandnumberrange-Aggregatingdataagainsttimeperiods

What concerns the Table Transformer macro, you may use another one and sort your data there using the "ORDER BY 'Column name' ASC" function. The date format in the second Table Transformer macro settings should correspond to the date format that you get after the first transformation.

If you are stuck, please refer to our support. Attach the page storage of your page and we'll look into your macros and their settings.

[Green_ Desmond]

That worked. I put a transformer around the other transformer, as you suggested.

I used the date format MM, getting rid of the year part. I don't need it here.

It always seems a bit weird wrapping a transformer with another to solve the problem and it always leaves me wondering what was the SQL to solve it in one transformer? Anyway, that is a value-add type  answer and I have a working solution! Thanks.

[Stiltsoft support]

It is often possible to use nested SELECTS to get rid of several macros (here is just a dummy example where we start row numbering from 3):

SELECT ('Number' + 2) AS 'Number', 'Key', 'Summary'

FROM (

SELECT ROWNUM() as 'Number', T1.'Key', T1.'Summary'

FROM T1)

So, you create a chain of SELECTs when in each external SELECT you refer to the virtual table created with the help of the internal SELECT.

The same result may be achieved using two separate macros - for some people it's easier to do because you can use the T1.'Column name' structure only in the very first internal SELECT. In other SELECTS there is no T1 table, so we use 'Column name' structure. And it means no autocomplete, more typos, and so on. And if you have separate macros, then you can always use the T1.'Column name' structure.

The issue when you can't keep everything in one query may be if you use grouping inside your queries or play with different data formats - such formatted cells may be recognized as strings after the transformation. So, a new separate macro helps to treat a transformed table properly in the simplest way.