Jump to content

NthLargest functions nested problem

Yates Guan

Recommended Posts

Hi teams:


I'm making a Cross table, and my data is partitioned by date.

I had a problem when I tried to get the 95%th largest value of data.

The problem is

If there is only one day of data, the result is correct, and when I include more days, the result becomes the wrong value.


This is the formula I used NthLargest([FE_TPT],Integer(ROUND(Count([FE_TPT]) * 0.05,0)) + 1) as [FE]


I guess it is because Count does not slices [EF_TPT] by time when functions are nested.

but I do not know how to deal with it, could you please give me some suggestions


attachment is my test data.

Link to comment
Share on other sites

Thank you very much, I've already tried this methodbut I may have misdescribed my problem.


The result should have a corresponding value in the source data.


For example, in your result, the value of 6/19/21 is 39.05. 39.05 does not exist in the source data.


It would be great if I could get to 39,  because 39 exists in the source data.


Please give me some advice about that.


thank you again.

Link to comment
Share on other sites

Of course, attachment is my test data.I appended it below the question.


I am sorry about the  :


NthLargest([Val],Integer(Count([Val]) * 0.05))


When you are using count operation without considering other column values, it will take the count considering the whole rows. 


So when you limit the date, the result change.

Link to comment
Share on other sites

Yes, you are right.


Filtering is causing an issue in this case. If you always want to use your full data set you could turn of the filtering in the cross table. Do this in the "properties -> data -> limit data using filtering". Does this already solve your problem


The percentile functions still works fine when you use filtering... But I know that you want the value from the table... 


Yes, thanks. Happy weekend :)

Link to comment
Share on other sites

Good morning.


I am sorry about that, I need to use the Filter.


Look hereIf I use the:


Count([FE_TPT]) THEN First([Value]) OVER (Intersect([Axis.Columns]))


the COUNT is expected,


but when I use the:


Count([FE_TPT]) THEN First([FE_TPT]) OVER (Intersect([Axis.Columns]))


It tells me:


Expected ')' but found 'THEN' on line2 character 18.


I'm not really sure what's going onmay be the over function cannot be added as the parameter of other function like NthLargest

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...