Request for SUMProduct function

johan_vangerwen
johan_vangerwen
in Modeling

Currently only when you use Time you will be able to use PREVIOUS() or alike function. These functions doesn't apply to Lists but at CBRE I have a case where do need a similar function. Today the SUM function, which doesn't allow for Product, therefor my enhancement request to create a function alike Excel function SumProduct 

Tagged:

Best Answer

  • johan_vangerwen
    johan_vangerwen
    Answer ✓

    Hi Paul,

     

    Thank you for your response! I had another look at it and actually I didn't describe the case correctly though what I need is correct: like the Lag function which can reference the its own LineItem, I also need a solution for Lists. I have created a solution now using:

    1. a module with Line Items equal to the leave members of the Lists,

    2. a module with collect() for the Line Items of the module in 1 and

    3. a module to map the List items with the Line items subset.

     

    So I have a structured workaround though with some restrians:

    - Only format is number (because collect() is limited to number format)

    - If a new List item is added, additional attention for maintenance is needed (add Line Item in the first module.

    - Volume: for each case you need to setup this structure.

     

    We have a cumulate() function which is just summing the values. In my particular case it would be needed not to sum but to product (multiply) the items (so it is not Excel sumproduct, my mistake). 

     

    I will close this "ticket" and create a new one if needed. Currently it is low priority.

     

    Kind regards,

    Johan

Answers

  • PaulRitner
    PaulRitner

    Hi,

     

    Can you provide an example?  Are you saying that you want to, for example, get a value from the previous list item (for use/consideration on the current list item)?

     

    If the list is stable, sometimes there are opportunities to do this.  For example, if the Code or the Name are a sequential/predictible value (even if they have to be occasionally reset via a bulk process), then you can use a lookup to get the Previous Item's (whatever value).  More specifically, if I'm on Item 5, then I can calculate the value of Item 4, and then use a FindItem for the LOOKUP.  

     

    In any case, we can explore more if you can provide some more context.

     

    Cheers!

     

    Paul

Categories