Querying a Transaction log

PrevContributor
PrevContributor
in Modeling

I am using a numbered list as the dimension for a transaction log which stores changes to a people count line item applying to 7 dimensions (list items). The numbered list has each of the 7 list items as a property (format type list attached to the appropriate list item). I want to populate a line item that has been created with just 2 of the dimensions where the remaining 5 dimensions are each fixed to the value of dimensionless line items in another module. So if my dimensions are D1 to D7, the line item I want to populate, P1, applies to only D1 and D2 and the item selections for D3 to D7 are fixed. Is there a formula I can write for P1 that will LOOKUP the transaction log on the properties for D3 to D7 matching the selections for D3 to D7 and then return the SUM of the people count line item? Note that the whole point here is to keep the size of my line items as low as possible. I do not want P1 to apply to D3 to D7 so please avoid suggesting a fix that requires doing so.

Tagged:

Answers

  • peter_mcanena
    peter_mcanena
    Yes, you can do this. In the module that's dimensioned by D1, D2, you say
    Transaction Log.Data to Sum[SUM: Numbered List.D1, SUM: Numbered List.D2]
    This will pick up D1 and D2 from the context of the module you're in.
  • PrevContributor
    PrevContributor
    Thanks for responding Peter. Unfortunately your suggestion sums across all values of D3 to D7 whereas what I am looking for is a formula that returns only those values which match on D3 to D7.
  • peter_mcanena
    peter_mcanena
    Right - sorry, I see what you're trying to do now.

    The formula should be
    Transaction Log.Data to Sum[SUM: Numbered List.D1, SUM: Numbered List.D2, SUM: Numbered List.D3, SUM: Numbered List.D4, SUM: Numbered List.D5, SUM: Numbered List.D6, SUM: Numbered List.D7,
                                LOOKUP: Dimensionless Module.D3, LOOKUP: Dimensionless Module.D4, LOOKUP: Dimensionless Module.D5, LOOKUP: Dimensionless Module.D6, LOOKUP: Dimensionless Module.D7]
    where Dimensionless Module is the module where you've stored the fixed values of D3 to D7.

    That will sum on all seven dimensions, picking up D1 and D2 from the context of the target module and the remaining five from the dimensionless module.

    One thing to note is that Anaplan don't recommend combining SUM and LOOKUP in the same formula. Apparently it can lead to perrformance problems. So be wary of overusing this type of solution. However I use something similar all the time (with lots of SUMs but just one LOOKUP) and it's never caused me any performance problems.


    PS: That formula above is not very legible so I'll give it again, in elliptical form:
    Transaction Log.Data to Sum[SUM: Numbered List.D1, SUM: Numbered List.D2, ..., SUM: Numbered List.D7,
                                LOOKUP: Dimensionless Module.D3, LOOKUP: Dimensionless Module.D4, ..., LOOKUP: Dimensionless Module.D7]

Categories