3 Replies Latest reply on Aug 28, 2018 9:20 AM by Shinichiro Murakami

# Moving Percentile as a Calculated Field within "Data Source"

Hi,

How can I compute a moving percentile by week and group?

I would like to have this computation as an calculated field within the Data Source sheet.

The aim is to compare  the 0.75 percentile of week X to the 0.75 percentile of week (X-1 to X-3).

WW34 --> WW33 - WW31

WW33 --> WW32 - WW30

etc.

Computing the 75% percentile of week X for a group works but I struggle to calculate a Moving Percentile.

Does someone have an idea how to solve this?

Martin

• ###### 1. Re: Moving Percentile as a Calculated Field within "Data Source"

HI Martin,

You said row level, which makes it pretty much troublesome but something like this helps?

Thanks,

Shin

• ###### 2. Re: Moving Percentile as a Calculated Field within "Data Source"

Hi Shin,

Mhhhh, my aim was to have the Moving Percentile value in a calculated field within the data source sheet and not in a table.

Here some background information on what I am trying to achieve.

We have too many combinations in our weekly data and therefore we need to focus on the bad combinations.

Having the value from "week" and "previous 3 weeks" we are then able to compare these two values:

e.g.

"week" 75% percentile --> 60

"previous 3 weeks" 75% percentile --> 50

The Increase of "week" compared to "previous 3 weeks" is 20%.

If we have the increase or decrease numbers we are then able to rank those and we can work through starting with the worst one (using the ranking as a filter)

Do you have an idea how to set up a formula for this?

I have tried the WINDOW_PERCENTILE function but it seems only to work within a table but not for a calculated field.

Do you have an idea how to set up a formula for this?

• ###### 3. Re: Moving Percentile as a Calculated Field within "Data Source"

Window calc only brings the aggregation of already aggregated value.

Is that what you expect ? or you need to calc row data level percentile across three past weeks..

My understanding was latter, that's why I spent long time to think out the solution.

If case is latter, I think mine is logically matching your request ?

And again, window calc only brings the aggregation of aggregation.

Thanks,

Shin