Not certain I fully understand but see the attached approach
I did it in table form so I can see the data but you can change it to a plot
this is the formula - since running formulas are tables calc you would need to use window_percentile - the table version of pwercentils
in table for it returns this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Sample_data_v10.2.twbx 237.9 KB
I need to confirm one thing.
Your avg have multiple layers.
1. Per [ID, time, Date] within [ID x Date]
2 Per [ID x Date] within [Location x Date]
3 running avg for [Location x Date]
Question is your percentile calc is supposed to be calculated at level of 1 or 2?
Sorry, I am not able to answer to your question. I will try to explain it another way. I will simplify it.
The resulting data represented as table have to be like this:
To calculate the 25th percentile for Location 1 :
for day 2018-01-01 - from source table I take the data only for 2018-01-01 and calculate average Amount for each ID so I have one value per ID. And then calculate 25th percentile of all IDs. The result is one number which I write in the resulting table next to 2018-01-01
for day 2018-01-02 - from source table I take the data from 2018-01-01 to 2018-01-02 and do the above calculations then write the result next to 2018-01-02
for day 2018-01-03 - from source table I take the data from 2018-01-01 to 2018-01-03 and do the above calculations then write the result next to 2018-01-03
for day 2018-04-17 - from source table I take the data from 2018-01-01 to 2018-04-17 and do the above calculations then write the result next to 2018-04-17
Maybe it will be easier to calculate it in the database using a loop instead of using Tableau.