Hi everyone,
I am making calculated field for calculation rolling 30 days revenue and using the formula windowSum(sum(totalAmount),[orderCreated ASC],30,0), but it returns me sum of revenue for not last 30 calendar days, but 30 date records in dataset. So if there are some days without any sales for example weekend it will calculate rolling sum for 38-40 days instead of 30.
Do you know any tricks to solve this issue?
Can you add a filter to your visual to limit do only 30 days?
Similarly you could look to add an ifelse to your calculation to only sum values that are in 30 days.
windowSum(sum(ifelse(dateDiff(orderCreated, now(),'DD')<30, totalAmount,0),[orderCreated ASC],30,0)
1 Like
thank you so much @Max, I am afraid it won’t help in my case as I have to show barchart with a revenue and rolling 30 days revenue for a given day on 30-days timeline. Suggested formula will calculate R30 for today I believe? And If I limit a visual to only 30 days, then R30 revenue for the very left column will be equal to actual revenue for that date.
Hi @PetroOk1 - if you create a calculated field say, AsofDate which is equal to orderCreated. Then use the formula:
windowSum(sum(ifelse(dateDiff(orderCreated,AsofDate,'DD')<30, totalAmount,0)),[orderCreated ASC],30,0)
Does it help?
1 Like
Thank you for your help @debapc , but unfortunately it works exactly like my previous calculation field.
But below you can see orderCreated, amount of Orders per day, my Orders R30 calc, your suggested Orders R30 calc and expected result in the last column.
Basicaly goal is to get value from the last column
Hi all!
question is still actual 
1 Like
If there are gaps in your data, could you possibly use count(distinct order_date) as the divisor - then use this to create the rolling average for the period? So this would calcualte the average for every day you have a sale…this will create issues if you want averages per day, over a monht (assuming 30 days), but it will give you an average where you have at least one sale per day.
If you use this, then this would only calculate an average on days there are a sale, so this would mean exlcuding an dates (weekends) where there are no sales.
Otherwise, could you use an ifelse (is not [saturday, sunday]…{plus additional logic}) to achieve what you need?
Thanks
Q
1 Like