KPI's Mapping from multiple lists

HI,

In my datahub I have transaction list that I deconstruct into financial elements, profit centre, cost centre, gl account, order number in a mapping table.

I have to report KPI's that are built up from multiple GL accounts and multiple Internal Orders, and I am wondering what approach to take to create a secondary KPI mapping from the original transactions?

I have tried using a List Property module but it falls down where there is duplication in one of the lists being mapped for a KPI. In this example the same Internal Orders are used for both KPI's but the GL04 is different.

KPI	GL04	IO
AAHEDC	O13	807001321
AAHEDC	O13	807001322
AAHEDC	O13	807001404
AAHEDC	O13	807001405
AAHEDC	O13	807000209
AAHEDC	O13	807001261
AAHEDC	O13	807001294
Ad Hocs	O15	807001321
Ad Hocs	O15	807001322
Ad Hocs	O15	807001404
Ad Hocs	O15	807001405
Ad Hocs	O15	807000209
Ad Hocs	O15	807001261
Ad Hocs	O15	807001294

Another idea I had was to create a module dimensioned with all 3 lists, (KPI, GL04 and Order Number) with a Boolean line item to capture the valid combinations, but I couldn't work out how to map that into an output using my original transactions.

Any suggestions would be greatly appreciated!

Accepted answers

All comments

JaredDolich

@joshuastockwell I think your idea at the end is really close to the right solution but instead of building one module with all three lists, use the transaction module to create a list of combinations. Then create a system module using that list and create the Booleans or transformations you're looking for. If you haven't already, you can also read up on the DISCO method, by @DavidSmith. It's one of the best modeling techniques Anaplan has to offer.

Let me know if that's close to what you were asking for. If so, I can use your data to create an example.

nathan_rudman

can you explain more your KPIs ? Is it just summing different transactions depending on their GL/other properties ?

usman.zia

Hi @joshuastockwell

You need to be very careful when combining so many dimensions to create a module but if the number of items are not expected to grow massively then this will be ok.

As @nathan_rudman has requested it would be really helpful to know the purpose of the KPIs as there may be a more efficient solution.

Equally as @JaredDolich method of creating combinations and using a boolean to identify is also great and booleans are the most efficient calculations for performance!

Thanks,

Usman

joshuastockwell

Hi Jared, I think what you're saying makes sense.

in my transactions module I create a new column of concatenated GL and IO combinations (KPI Mapping column below).

Then create a new list in the same format and map that to my KPI's

nathan_rudman

Personally, I try to avoid to have to maintain list, I prefer the instantaneous and safety of formula.

You could create a mapping module by dimensioned by GL/IO/CC/whatever with one line item formatted as KPI.

Then you have one KPI line item in your transaction module and you bring the KPI like this: mapping module.KPI[LOOKUP:GL,LOOKUP:whatever]

Then you can simply SUM in this line item:

transaction.value[SUM:KPI]

joshuastockwell

Hi, thanks for your help so far.

The purpose is an alternate P&L report for a subset (3 profit centres) and will go to our regulator. The KPI's will roll up to a summary lines of Direct Revenue and specific Cost lines, EBIT etc.

ChrisAHeathcote

The use of a concatenated unique identifiers adheres to best practice when creating mapping modules. However, the use of nested dimensions does work and will allow the user to map successfully across multiple dimensions without having to create a series of data transformations.
I have used this approach successfully in my own models. However, try not to use more than one line item in order to limit the number of cells in the module.

usman.zia

Hi @joshuastockwell

Assuming your 3 profit centres are in the original list module you will be able to create module to report KPIs just for these.

It is possible to simplify what you would like to achieve by creating a subset of the list of the 3 profit centres.

This will then allow you to create a module dimensioned by this subset where you can reference the original reporting module to create your alternate report KPIs.

I hope this helps,

Thanks,

Usman

Quick Links

Can you help?

Unanswered questions

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…