Hi!
I have a dataset that includes region, units, sales, price and a fulfilment_date. I want to be able to calculate the 10th percentile of the sales in the previous 13 wks.
So far I have this calculation but I’m not able to make sure it’s getting the last 13 wks:
periodToDatePercentile({sales},10, {fulfilment_date}, WEEK)
How can I alter this to calculate the 10th percentile in the last 13 wks?
Hello @bunmis
Could you try doing this with a conditional calculation? For example:
ifelse(fulfilment_date >= addDateTime(-13, 'WK', fulfilment_date) AND fulfilment_date <= now(), percentile({Sales}, 10, [fulfilment_date]), NULL)
Let me know if you hit a mismatch aggregation error. Also, I added the partition for fulfilment_date but I’m not sure if you will need it.
Hello!
This look good, I tried to add this logic, but because my sales is also a calculated field, it says I can’t have a nested aggregation.
Hello @bunmis
Can you share the calculation for Sales? There might be a way to incorporate it to get a around the error.
Hello @bunmis
Are you able to share your Sales calculation?
Hi!
Sorry for my delayed response. Sales is a column that is calculated by saying if the Item is A then use projected_sales_value, else if item is B, then use actual_sales_value.
Hey @bunmis
Are you still working on this or were you able to find a solution?
You could try getting around the error by doing the following:
percentile(ifelse(fulfilment_date >= addDateTime(-13, 'WK', fulfilment_date) AND fulfilment_date <= now(), {Sales}, NULL), 10, [fulfilment_date])