Perform calculations where expression uses dimension

[nsm1234567]

Hi There,

Not sure if I've titled this correctly, but I'm having difficulty calculating a current ratio due to the way my data is formatted and I'm wondering if there's a workaround.  My data looks like the image below.  I have expressions which look as follows:

Current Assets:

=
//If the account is normally an asset account and the balance is positive, then sum the balance
(if(([Adjusted Category]='Current Asset' or
[Adjusted Category]='Current Liabilities') and sum(Balance)>0,


sum({<[Account Type]={"Balance Sheet"}>}if([Fin Period] =max(total([Fin Period])), Balance,0))))

Current Liabilities:

=
FABS(//If the account is normally an asset account and the balance is positive, then sum the balance
(if(([Adjusted Category]='Current Asset' or
[Adjusted Category]='Current Liabilities') and sum(Balance)<0,
sum({<[Account Type]={"Balance Sheet"}>}if([Fin Period] =max(total([Fin Period])), Balance,0)))))

What I want to do is divide current assets by current liabilities to get the current ratio, but my data doesn't facilitate this.  If I remove "adjusted category" the results disappear altogether.  Can someone let me know where I'm going wrong?

[mazacini]

Hi Nathan

Unfortunately, I only have personal edition, so cannot open your file.

ould it help if you flagged the transactions with a 'Current Ratio Flag'?

I would join the Adjusted Category to the BALANCES table, then drop that field from the INLFED-1 table.

Then I would create a 'Current Ratio Flag' field:

if(AC=CA,if(B>0,1,2),if(AC=CL,if(B>0,1,2),0) as [Current Ratio Flag]

(Note AC-Adjusted Category, B = Balance, CA = Current Assets, CL = Current Liabilities)

This would then flag all non CA/CL transactions as 0, all positive CA/CL as 1, and all non-positive CA/CL as 2.

Then your formula would use Set Analysis Sum({<[Adjusted Category]={1}>}Balance)/((Sum({<[Adjusted Category]={1}>}Balance)+Sum({<[Adjusted Category]={2}>}Balance))

Would that work?

Joe