nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Last 12 months?

Macamba

At the moment we live in October. If I have data over the last 12 months, I would like to display it beginning with September, and then going back in time. Is this possible?

Find more posts tagged with

Comments

thuston

Create a computed column that will be your group/sort value for the month.

Use BirtDateTime.diffMonth(row["column"],BirtDateTime.today()) to get a nice order where september=1, august=2, etc.

Macamba

Thanks. That solved my problem, but created another one.

Originally I used a field end_month, which is a number depicting the month (e.g. 1 is january, 12 is december). I have a end_timestamp field which I used, but that breaks up my group by and order by clauses in my SQL query :`-(. I can have several records with the month number 9, but all different timestamps.

thuston

I'm not sure I understand. If you compare against a constant value like TODAY(), how do you get wrong values?
Also, if you do it as a computed column on the dataset, you can't break your SQL.

Macamba

<blockquote class='ipsBlockquote' data-author="'thuston'" data-cid="69220" data-time="1286803147" data-date="11 October 2010 - 06:19 AM">
I'm not sure I understand. If you compare against a constant value like TODAY(), how do you get wrong values? 
Also, if you do it as a computed column on the dataset, you can't break your SQL. </blockquote>
 
It has to do with the query I use: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT DISTINCT organisation,
timestamp,
-- month,
priority,
AVG (downtime)

FROM public.incidents

GROUP BY organisation,
timestamp,
-- month,
priority

ORDER BY organisation,
timestamp,
-- month,
priority
</pre>
Originally, I used month that was a number (1 .. 12). Now I use a timestamp which occurs as 4 sep 2009 10:51 and 24 sep 2009 17:09. Grouping on month would give one record in this example. Grouping on timestamp as I do now gives me two records. 
 
I will probably solve this by doing the grouping in the report editor.

thuston

You could do a similar computed column with your Month value. 
 
You just need to take what you have and get it to give a simple ordering. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var myorder = BirtDateTime.month(BirtDateTime.today()) - row["month"];
if ( myorder <= 0 )
myorder = 12 + myorder;
myorder;</pre>