# Palo.data.avg on a date range

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Palo.data.avg on a date range

Hello everyone,

I am having problems with performance of my current calculations and I'm seeking for any help with improving it.

So the problem is I have to calculate an average of Product weight for a given date range, however in single day, product can be produced up to 3 times (3 shifts a day).
I've been trying to use PALO.DATA.AVG, however I cannot make it to work on an array of days.

What I am doing right now is I'm getting total weight from an array of days using SUM(PALO.DATAV()) and then I divide it by number of days product was manufactured per each shift (DFILTER), however in order to get a real number, I need to calculate it for each of 3 shifts separately and then sum it up.

This is how it looks like in a simplified view:

Sum(palo.datav(....,@DateRange,"Weight",....)) / (
PALO.SUBSETSIZE(..."DATE"...,PALO.TFILTER(..@DateRange...),PALO.DFILTER(...."Shift 1"...)) +
PALO.SUBSETSIZE(..."DATE"...,PALO.TFILTER(..@DateRange...),PALO.DFILTER(...."Shift 2"...)) +
PALO.SUBSETSIZE(..."DATE"...,PALO.TFILTER(..@DateRange...),PALO.DFILTER(...."Shift 3"...))
)

I'm adding that formula in a dyna range and the performance is devastating, but I can't find any other way to get the average...

I would really appreciate if someone could help me.
• Hi sebkos,

Sorry I don't have answer for you question, but I want to use similar mechanism related to date range. I am not getting how to use AVG function. My query is-
Here a user selects start and end date and based on that it should populate the number on the committed order+Back order column.

I am using start date as @varStart and end as @varEnd. I want to sum the numbers in between two selected dates. for example- SO(sales order) arriving at different dates in between two selected dates and i want to sum all the numbers.

example- based on the input dates in picture, if the SO arrives at 15 feb= quantity 1000, 20 feb-quantity 2200, 21 march- quantity-3000, 1 apr-quantity 1000 etc etc.., it should sum the numbers (all quantities)

What kind of formula will I use ?I searched for a function and I got SUMIF, but it selected a range whereas I have a variable..so I am confused and Item list is a dynarange. My cube structure is- DAY, MEASURE(quantity), LOCATION, CUSTOMER, ITEM.
Files
• Stock.PNG