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

      (10.59 kB, downloaded 29 times, last: )