Forum Discussion

ajackett's avatar
ajackett
New Contributor III
5 months ago

Combining data from 2 cubes to calculate Creditor days

Hi all, 

I'm after a bit of advice. We have a STAT cube which has BS and PL data and there is a MGMT cube which has PL data but goes to the level of Regions (UD1) and Departments (UD2). 

There is a Stat entity structure and a Mgmt entity structure, with the only difference being S_ and M_ prefixes. So Group is S_GRP and M_GRP respectively. 

I have been requested to build a report to calculate creditor outstanding days by taking the Creditors balance in S_GRP and dividing by COS in M_GRP split by Regions (UD1) and multiplying by days in the month. 

I started to create some new reporting accounts to calculate these, but then I realised there's a mix of dimensions between the cubes so it's not as straightforward as using api.data.calculate etc. 

Does this seem like something that is possible? Currently the users utilise the XFGetCell function in the add-in, but want to have something more automated. 

Any advice is appreciated. 

Thanks

Finance
XFBR
  • TheJonG's avatar
    TheJonG
    Contributor III
    5 months ago

    I may need a bit more information but it sounds like this should be possible by using the Unbalanced functions which are used within an api.Data.Calculate with data buffer which have different dimensionalities. Here is an example:

    Dim povEntity As String = api.Pov.Entity.Name
Dim S_povEntity As String = $"S_{povEntity}

api.Data.Calculcate($"A#OutstandingDays = Divide(1, DivideUnbalanced(A#COS, E#{s_povEntity}:A#Creditors:U1#None:U2#None, U1#None:U2#None)")

    The above formula may seem a little confusing due to the order of operations that is required for the unbalanced functions. The idea behind the unbalanced functions is that one of the arguments in your formula has more dimensions defined than the other and this argument always has to be listed second so we need to flip the division in this case, which is why we take 1 divided by the result. The result of this will be that we can divide the two data sets and still have UD1 and UD2 detail.