cancel
Showing results for 
Search instead for 
Did you mean: 

Rank function and get previous value

former_member597137
Participant
0 Kudos

Hi all,

I have a simple tab with 4 columns: one dimension, one measure, a rank function applied to the measure and a fourth column for which I'm not getting the right result.

As you can see, I have 3 times rank 1 because the measure is the same for those dimension's value (which is fine).

What I want, for the 4th column, is to display the measure for rank 3.

As the rank 3 does not exist, I want to display the previous rank existing, which is rank 1, so it should diplay the value "36".

Is there a way to create a formula to get this value?

I tried something like this:

= If IsNull(

If IsNull([measure] where ([rank]=3) in report) Then [measure] where ([rank]=2))

Then [measure] where ([rank]=1)

In this case, it works but my data set is quite big and I might have many multiples values for the measure so my formula would be too big if I want to be sure to not miss any multiple value ..

Is there an easier way to do it?

Something like : If IsNull(***) then get previous existing rank value .. This is the logic I want to apply to my variable ..

Thanks in advance for your help!!!

Marc

Accepted Solutions (0)

Answers (2)

Answers (2)

amitrathi239
Active Contributor
0 Kudos

Sort the table on rank variable in ascending order.After that use below formula in Rank 3 vairable.

=runningcount([Your Rank variable])

former_member198519
Active Contributor
0 Kudos

is there only one measure object in the table? if no, you can try:

= rank([Measure1];[Measure2];([Secteur]))

former_member597137
Participant
0 Kudos

Yes, there is only one measure..

Thank you for your help