Percentile formula not recognizing filter

[Ash Mishra]

While using P90 it recognizes when filters are selected and recalculates based on the unfiltered data, it does not work for calculated column using Percentile function.

I am using slider to allow adjusting percentile. When set to 90, both P90, and Custom %Tile columns are exactly the same. However, when unchecked one of the filter for Shift, it recalculates P90 but Custom %Tile remain the same. Is this an expected behavior What am I missing DXP is attached.

[Fabian Duerr]

Yes, this is expected.

The summary table is dynamical. So it's calculating values based on your set filters. Your calculated column is static. So values don't change when you apply filters.

If you would set the shift also on the y-axis and use thePercentile([DELTA],90) OVER ([EMPLOYEE],[sHIFT]) in your calculated column then the values would be the same. As the calculation of your column would not be affected by setting a filter on shift.

[Ash Mishra]

Hi Fab,

 

Since I won't know ahead of time what filters users are going to select, is it possible to dynamically make them part of my calculated column Here shift is an example of a filter, but I could have 5 other filters, that users may or may not select.

 

 

[Fabian Duerr]

If you want it to be dynamical then stick with the summary table.

 

Simply use your expression on the X-Axis:

 

Percentile([DELTA],${Percentile}) 

 

This works fine with filtering. Here the OVER part is not needed since your data will be grouped automatically by the values on the y-axis (here EMPLOYEE)

[Ash Mishra]

That works but does not allow you to control it via document property. 

 

Percentile([DELTA],${Percentile}) as [CUSTOM %TILE] forces the ${Percentile} to be hard coded to 90. Changing value via document property then have no effect.

[Fabian Duerr]

It works just fine in my dxp file. When I change the slider the X-axis updates and also the values in the table are recalcuted.

[Ash Mishra]

I got it to work! Thanks to my colleague who was able to help me out.

Basically what I had to do was not replace with Percentile([DELTA],${Percentile}) but replace withPercentile([DELTA],DocumentProperty("Percentile"))

Thanks for your help Fab.