NthLargest functions nested problem

[Yates Guan]

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.

[Fabian Duerr]

Please try the Percentile() function.

For your case: Percentile([EF_TPT],95)

[Yates Guan]

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.

[Yates Guan]

Note: There are decimals

[Fabian Duerr]

My apologies.

 

For my example above the following works fine:

 

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

 

It returns 19 and 39.

 

Can you share some of your data to understand where you incorrect values are coming from Just seeing your results doesn't help much.

[Yates Guan]

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.

[Yates Guan]

by the way happy weekend!

[Fabian Duerr]

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 :)

[Yates Guan]

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:

 

NthLargest([FE_TPT],
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