How to filter on last 12 rows of data...

[far972]

Hello All,

Simple question I hope, I am trying to filter a microchart on the last 12 rows worth of data, which are formatted in a non standard date format; 'year-month' (2009-11, 2009-12, 2010-01).  So I can't use something like,  [Date] >= _add_months (Current_Date, -11)

I tried sorting the year-month column in descending order and then using the following filter

(running-count(count(rows)))<13

This gets me the last 12 rows, but the microchart displays them in the reverse order...

Is there a simple way to get the *last* 12 rows, and have them display in the right order?

Thanks,
Mike

[MFGF]

Hi,

Here's an idea:

1. Change your sort on year-month to Ascending.

2. Add a Query Calculation called RunCount with the expression running-count([Year-Month Item])

3. Add another Query Calculation called TotCount with the expression count([Year-Month Item] for report)

4. Define your filter as [RunCount] > [TotCount] - 12 and set the timing to After Auto Aggregation.

Does this give you what you need?

Regards,

MF.

[far972]

Thanks for your response.

Unfortunately, when I tried to implement this, it is only returning the last months worth of data (1 row)...

I am going to play around with it a bit more and see if I can get it to work.

Thanks again,
Mike

[MFGF]

If these two items are not included as list columns, you may need to add them to the properties of the list for them to be evaluated correctly.  To do this, select the List object in the report, and go to the 'Properties' property and enable both items.

If this doesn't fix the issue, add both items to the list so you can see the values being calculated, and temporarily disable the filter.  Hopefully this will help you see what's going on.

Regards,

MF.