BusinessObjects Board

Need Higest Date and second highest date in a new column on same line

This should sum it up.
So I got several keys with Logged On dates.
The goal is to find the last date and the second last date and see how many days have past.
Tried using a Rank function and that get 1 and 2 but it will not put it on the same line.

So all these lines that are not last date or second last date need to go.
And for every key I need 3 columns. 1 for last date, 1 for 2nd last date and 1 for the number of days between them.

ps: actual list is much longer.

did you use the “rank” function as a screen related graphical function or a pure function (int Rank(measure;[ranking_dims][;Top|Bottom][;(reset_dims)]))?

with the latter, it should be possible to set the base for a solution; then you can nest it with a variable or the like, e.g. like this: [Variable] where([Rank_variable]=2)

Hi @TOJ , thanks for responding!

Well I have last used which is : =Max([Logged On]) In ([Key])

And I have Prev Log which is : =[Logged On] Where ([LogRank]=2)

LogRank is setup as : =Rank([Logged On])
So it is very simple Rank function. Maybe this is the problem.

For 1 given key and logged On it looks like :
Here you see that last date is on every row and prev only on the line where the logrank =2

I think it would be better of prev was also on every line.
Because when you take the log rank and the Loggedon out:

2 lines not 1
And if I make a last based on where rank is 1 it makes it worse
Now I have even 3 rows.

Even if I take out the last and have 2 rows again.
Filtering out the blank on Prev Log result in no lines at all :thinking:

Hi @rpinxt
by using more parameters of the rank function (int Rank(measure;[ranking_dims][;Top|Bottom][;(reset_dims)])), maybe it can be solved
when using it, I have noticed that sometimes the Top|Bottom parameter, although indicated optional, must be used for getting it work correctly

Ok thanks @TOJ but could you put it a bit more in context of my example?

I see you start your measure with int.
My webi does not accept this.
Also i have no measure I want to rank a field.
=Rank([Logged On];Top) would be enough??

Items will only roll up when they are measures. You sometimes need to specify an aggregation projection (although max would default to max) so Prev Log as : =Max([Logged On] Where ([LogRank]=2)) may work in your instance.

Interesting take @MarkP .
Tried it:
=Max([Logged On]) Where ([LogRank]=2)
indeed defaults to a measure.
But it still does nog show it ob all rows.

And when you take out the old prev log:

So unfortunately for some reason it stays empty then.

Isn’t there a way yo just capture that value 2nd in rank as a variable just as a fixed number.
Without other context of the report.

Hi @rpinxt
I think you can spread out in the 2 rows table the content of the column Prev Log New on all rows by:
max([Prev Log New]) in([Key])
with the default table setting (avoid duplicate rows aggregation = false (unticked)), it would shrink to one row

Well this looks promising @TOJ

However when I take out the old Prev Log:

I do not understand why it does that.
Why does it need that field in? Is it because that field holds the rank formula??

Well building on that thought I tried implementing the logic of LogRank into the Prev logic like.
Prev Log Var = Max([Logged On]) Where ( Rank([Logged On])=2)

I called it Log Var because to get it showing at alle rows I have to max this logic of @MarkP with what @TOJ said. Don’t think you can do a max on a max.
So first this max.

And then for Prev Log New I max that var again:
=Max([Prev Log Var]) In ([Key])

And then:
Starts good, now taking out the old Prev Log as that logic is already in the Var logic.

Must be that blank line which always seems to come in for the old Prev Log.
And I have nog clue why this blank line pops up.

Can we somehow lose that blank so there is no #MULTI error?

maybe it works when changing this:
Max([Prev Log Var] In ([Key])) rather than Max([Prev Log Var]) In ([Key])

Unfortunately same #MULTIVLUE