# 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

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.

#MULTI
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