Max Wert

[#1]

Hello together,

I need your help. I have the following function:

Max(Datenpunkt_ID)

it´s show me the correct answer with 214.

If I use the function:

Max(Datenpunkt_ID)-10

It´s show me the answer 204, thats correct

Now I need a function that I can delete the 10 high values, because my Datenpunkt_ID table look like this:

Datenpunkt_ID

214

214

214

213

212

212

212

211

210

209

208

207

208

Has anybody an idea?

[swuehl]

Not sure what you mean with '10 high values' with regard to your calculation of Max(Datenpunkt_ID)-10, but maybe something like this?

DP:

LOAD * INLINE [

Datenpunkt_ID

214

214

214

213

212

212

212

211

210

209

208

207

208

203

201

200

];

LEFT JOIN

LOAD Max(Datenpunkt_ID)-10 as MaxID

Resident DP;

Final:

LOAD Datenpunkt_ID

Resident DP

Where Datenpunkt_ID < MaxID;

DROP TABLE DP;

[#3]

I need the result of 209. So I look for a function with a resolve.

The 10 highest max values should not be considered

[zhadrakas]

i think you should Change this part from

LEFT JOIN

LOAD Max(Datenpunkt_ID)-10 as MaxID

Resident DP;

//that will exclude the ID's > 204, which dont have to be the last 10 values

to

LEFT JOIN

LOAD Max(Datenpunkt_ID, 10) as MaxID

Resident DP;

//this will get you the real last 10 max values

[swuehl]

I think Max(Datenpunkt_ID,10) won't consider duplicates correctly.

Try

DP:

LOAD * INLINE [

Datenpunkt_ID

214

214

214

213

212

212

212

211

210

209

208

207

208

203

201

200

];

First 1 LOAD Datenpunkt_ID as MaxID

Resident DP

WHERE Recno() >= 10

ORDER BY Datenpunkt_ID desc

;

[#6]

Stefan your answer look correct with recno>=10

but how I can use the function in KPI

And Tim with your function I get the wrong answer, with duplicates include.

[swuehl]

Maybe like

=Max( Aggr( If(Rangesum(Above(Count(Datenpunkt_ID),0,Rowno())) >=10, Datenpunkt_ID), Datenpunkt_ID))

This assumes Datenpunkt_IDs are loaded in order desc.

If this is not the case, you would need to create a correct load order in the script or use sorted aggr() dimension.

The sortable Aggr function is finally here!

Instead of the Rangesum(Above(...)) part, you can also create a record counter in the resident table using Recno() and check against this field.