Accessing 5 last records (by Date) in a FM query suject through RS

[ccbarbeau]

Hi all,

I'm working on a dashboard and I'd like your help in creating a data item that takes the last 5 records of a table (the last 5 dates) and adds up another field in the table.
ex:
Date                 Value
2011/03/31       25.00
2011/03/30       25.00
2011/03/29       25.00
2011/03/28       25.00
2011/03/27       25.00
2011/03/26       25.00

The dataitem should bring up 125.00 as a total.

Can you help?

Thanks!

CC

[Lynn]

Is it a relational model? If so, take a look at the rank function. Use that to identify the latest five based on the date (such that the 5 you want show up with ranks 1 through 5).

Then you can interrogate the rank value to derive the proper total. Create a query item with the expression something like this:

Code Select Expand


if ( [RankValue] <= 5 ) then ( [Value] ) else ( 0 )


Good luck!

[ccbarbeau]

Thanks, that worked exactly like I wanted it to  :D

[ccbarbeau]

Maybe there is a small problem after all...

my rank function is skipping records!

My data looks like this:
DATE                   LINE                  VALUE
20110428   11      0
20110429   11                0
20110502   11      25
20110503   11      35
20110504   11      125

and my rank function: rank([DB].[Tablename].[Field])

result comes up like this:

DATE         VALUE         LINE
4               0                  11
3              125              11
1              25                11
2              35                11

Rank 5 is missing, and when I remove my ranking filter, I see a bunch more missing, yet I see the data in the table.  :-\

It's skipping lines with a value of 0... could it be a duplicate value issue? I've read about a dens_rank function but it doesn't seem to work in RS.

Any ideas?

[ccbarbeau]

Oh jeez, I just found the rank(distinct) function.

[cognostechie]

The Rank (without the distinct) should be fine but you have to set a filter in the query to say Rank <= 5.
Make the filter a Summary filter and the Rank data item should be set to 'after aggregation'.