Hello,
I want to calculate cumulative percentile of ‘aging’ column based on a date column by month. It is similar to YTD.
So lets say if there are 10 rows in January ,5 rows in Feb and 20 rows in March; then the Jan 90th Percentile YTD should be calculate for 10 rows,
Feb YTD should show 90th percentile for all 15 rows together and March 90th percentile for all 35 rows
So far, I have tried:
periodToDatePercentile({ageing}, 90, {step1_date}, MONTH) but it is coming empty.
Hello @sakshisl, my initial thought is that you might want to try including an end date for the function. Maybe if you tried using truncDate(‘MM’, {step_1_date}) as the end date, it would only calculate through the current month. Otherwise, based on the documentation, the end date will refer to now() so it will try calculating based on the current date. Let me know if this helps!
Hello DylanM, in end date it expects a date parameter only so I am getting a syntax error if I try that.
Hello @sakshisl, I see. Basically, you are going to have to utilize a different method to run this calculation. The periodToDatePercentile function is going to require a date field in order to run. If the reference field is not a date, there is no way for the calculation to understand what it is comparing.
Are you able to reformat the date in your dataset to bring in an actual datetime field?
Hello @sakshisl, we have not heard back from you in a few days, so I wanted to see if you had any remaining questions on this issue. If we do not receive a response in 3 days, I will archive this topic. Please let me know if you still require assistance and I can help guide you further.
hi, I only have one date reference. The end date is optional for the field. The formula is not able to capture cumulative 90th percentile. For now, I am trying to build this in the query since the output is only yielding month percentile not Year till month.
Hello @sakshisl, I appreciate the information. I know the end date is technically optional, but it seems like the best way for you to achieve your desired output. Since you only have a single date field, you could also try manipulating the end date to show as the first of the next month so it would contain the full month values. Something like this:
addDateTime(1, 'MM', addDateTime(-(extract('DD', {step_1_date})-1), 'DD', {step_1_date}))
Nesting the addDateTime functions like this would create the first day of the next month. For example, today is June 13, so it would subtract 12 days from today’s date to make June 1st. Then it would increase the value by a month to July 1st. Using a calculation like this to manipulate the calculation should help resolve the issue you are seeing.
I can include documentation on the extract and addDateTime calculations:
I’ll mark this as the solution, but keep me posted on your updates through altering the query. That is also an option to make this work. Thank you!