SUM based on Line Item Value

I have a line item that has a list value.  In another module, I would like to sum based on that list value -- without adding that list as a dimension in either module.  Is this possible?  I know this syntax isn't right, but it would be something along the lines of Source.Amount[SUM: IF Source.Line Item = List.Value]

Tagged:

Best Answer

  • Hi Jeffery,

    I agree with Grantham that a little more information about how you want to sum the data would is needed to fully answer your question.  However, have you tried using if statements to pull in values associated with your list formatted line item?   You would still need a line item with data reference (I'm assuming you have this), but you the formula in your target line item would look something like this:
    IF List = Items in the List.Item #1 then Value #1 ELSE IF List = Items in the List.Item #2 then Value #2  ELSE IF List = Items in the List.Item #3 then Value #3 ...

    Where:
    List formatted Line item = List
    Items in the List = Item #1, Item #2, Item #3
    Value associated with list item = Value #1, Value #2, Value #3

    Happy to provide further suggestions if you want to share more of what you're trying to do.

    Best,

    Hilary Renaux

Answers

  • [quote=Jeffrey Donoghue]I have a line item that has a list value.  In another module, I would like to sum based on that list value -- without adding that list as a dimension in either module.  Is this possible?  I know this syntax isn't right, but it would be something along the lines of Source.Amount[SUM: IF Source.Line Item = List.Value][/quote]
    Hi Jeffrey,

    I think you may need to expand on what you are doing here.

    But, 
    If the list is not a dimension in either module, then I would expect you  would still have a third module with the stored value for a list item?

    Then in the module without the list, create a line item that has 'format' referencing the source list and a line item for the resulting value (based on the lookup)

    LineitemValue = Source.Amount[LOOKUP: LineItemListFormatted]

    is this what you are after?