Hi,

There are probably different and better ways to achieve this, but a simple way I use is attributing IDs to your items and then calling them in the new dimension.

I’ve answered a similar question recently:

I created a short model to illustrate it:

Dim A and Dim B are the two source dimensions, Since they’re dimensions, all items within them are already unique, but I assumed they can have common items (Green and White).

Dim A.ID is a auto-generated integer unique property.

Dim B.’exclude duplicates’ checks if the Dim B.Name already exists or not in Dim.A to keep only the unique items.

Dim B.ID is a cumulation that starts after 100, considering that Dim A won’t be larger that 100 items, but you can add your own limit

Then on the Joined Dimension, I added 200 items and Joined Dimension.ID is a auto-generated integer unique property.

Then Joined Dimension.Name is just matching the IDs with Dim A and Dim B.

Note on the Joined Dimension:

Using this technique, the Joined Dimension could contain a lot of blank items (IDs with no match), in my example the Joined Dimension view is filtered on “non blank” items, and later on your model they would not have data so using the “Hide empty rows and columns” filter will also hide the useless blank Joined Dimension items.

Note on new accounts imported:

Any new item added to the 2 source dimensions will create a new unique ID, and then be added in the Joined dimension. So it automatically updates once a new P&L or balance sheet account appears in either of the two source transaction lists.

Hope this helps.

Cant you just do the reverse ?

E.g. have a full list of chart of accounts, then subset it into 2 subsets : 1 with pnl , 1 with balance sheet.

Usually coa tables are held outside of epm system, so usually all accounts are all in the same place for accounting

Hi Benoit and FPA,

Thanks for your input and apologies for my late reply. ​@FPA : I agree with you that it's probably best to divide the total COA into two subsets, but unfortunately that's not how it's currently set up for us in Pigment. We’ll have to work with two lists at the moment. It's a good candidate for later model improvements, but at the moment we have bigger fish to fry :-)

​@Benoit : thanks for your input. I might be able to make this work. I’ll give it a shot. Thanks so much for your input. 

Best, Bas

Hey Bas, 

An alternative option would be to, based on Benoit’s suggestion, build 2 cumulate properties. 
Your P&L property cumulates from 1 to X, and your BS property should start cumulating from X to Y. 
This gives every single P&L or BS item a unique ID. 

Then build a new dimension with at least that number of items that reflects Y (but add some buffer and maybe an automation to remind you when you’re reaching the end of the list). Then use the ITEM function to map the P&L and BS dimensions to the new combined dimension. 

The main downside of this solution is that in your new combined dimension, items can shift, so you need to make sure to never do any inputs on it. Today a BS account can be X + 100, but tomorrow it can be X + 101 if a new P&L account ia added. But you won’t have any white space.

Very similar to Benoit’s solution (which is nice solution as well) but with different up/downsides, so you can choose what fits you best. 

Alternatively, our product team is working on a feature to allow imports from lists to lists, so that removes the need for any workaround at all.