Hello All,
I am looking to calculate 95th percentile of a column Month over Month.
However, this should calculate last 12 months for each month over month rather than selecting the whole range.
Hi @saabhiji
Welcome to QuickSight Community .
Could you please try creating calculated field like below … below example find the 50th percentile of sales in each month .
percentile(Sales,50,[truncDate(‘MM’,{Order Date})])
50th Percentile & Median are the same just to ensure numbers are correct . And also applied filter applied on the visual to show only data for current year .
Thanks
Vinod
Hi @saabhiji
@apjvinod showed you how to calculate the desired percentile for each month. Is this what you were looking for?
Alternatively, if your intention was to compute the 95th percentile for each month based on the previous 12 months, I recommend performing a 12-month rolling percentile calculation directly within the database. This method involves analyzing a different frequency distribution (of 12 values) for each month and computing the 95th percentile. In essence, it entails looping through the data for each month to derive the percentile and then integrating these values back into the primary dataset.
Hope it helps,
GL
Hello @apjvinod ,
Thank you for the input. This looks like each month 95th percentile but i need a percentile every month for the last 12 months of each month.
For example: For Dec’23 - It should calculate Jan’23 to Dec’23 and in the same table it should Nov’23 for months Dec’22 to Nov’23 so on…
Hello @Asem ,
If you are looking for each month probably the above suggestion helps.
Hello @gillepa ,
It makes senses. However I add few calculations once the data is arrived and then consider the percentile based on the status if its open or closed. Will try this method.
Thank you.
Hello @apjvinod !
Were you able to try @gillepa 's solution, and if it worked could you mark their comment as the solution to help the community?
It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 3 business days this topic will be archived.
Hello Duncan,
I am new to community and wasn’t sure how it works. Thank you for checking back on this.
To answer your question. This method will work however I’ve not implemented yet because my data is huge and there is a possibility of timeout error when the data loads.
If the data is small in size then yes this will work.
1 Like