Hi,
Id like to take data from a source metric dimensioned by month, GL, department and project and map this into a P&L line item metric dimensioned by month. However, as mapping metrics must be formatted by the dimension of the target what Im unsure how to go about this.
In a different life I would create a list derived from the P&L line items and use this to format my mapping table but alas I dont think this is possible with Pigment.
Any thoughts on how best to approach this?
Thanks,
+2Hi Chris,
If I understand correctly, your input metric structure is Month x GL x Department x Project (Metric 1), and you want to get this into a metric structured Month x P&L Account (Metric 2)?
In this instance, because GL x Department x Project determine which P&L line costs need to sit in, you need to create a third Metric 3 by GL x Department x Project, with the data type P&L Account (Dimension), in which the correct P&L line for that intersection of GL, Department and Project.
Your syntax for Metric 2 is then ‘Metric 1’[BY: GL, Department, Project → Metric 3].
Best, William
Hi William,
That would definitely work if I was using a P&L Account dimension list. In this example I am not. The target is only dimensioned by Month and Versions.
+2Hi Chris,
I suppose then that you have one metric per P&L line? In that case your solution should be either
Metric 1[FILTER: Metric 3 = Account.”Account Line”][REMOVE SUM: GL, Department, Project]
or
Metric 1[FILTER: Metric 3a][REMOVE SUM: GL, Department, Project]
where you have a Metric 3 per metric in your P&L. I’d highly recommend the first approach, as it protects against errors better than the second. If metric 3a and 3b, for example, have the same intersection of Gl, Department, Project flagged then you’ll get wrong numbers, whereas the first approach guarantees each intersection only maps to a single P&L line.
Best, William
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
