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

Could you post an image of your table structure?

Joe

[nsm1234567]

Hey There,

Thanks for the reply.  Would the below suffice?  It shows all the dimensions I'm referencing  in the expression.

  

[#4]

Can you attach a sample file pls for review?

Edit: Also why dont you just put them in a Text Box and kinda play around with alignment and cosmetics stuff. I think that might work.Cause you dont need a Table here I believe

[petter]

Your assumption is actually not entirely correct. It is quite easy with your current data to calculate the ratio.

Just add an extra expression in your chart and define it like this:   Column(2) / Column(1)

I have added % formatting on the number of the new expression:

It is possible to simplify the Current Assets and Current Liabilities expressions significantly and

make them much speedier by resorting only to Set Expressions and removing the IFs. However

since you specifically asked for calculation of the ratio I leave that for another time....

[nsm1234567]

Hey Petter,

Thanks for the response.  The crux of the issue I'm having is that none of those aggregations work for me.  I've pasted some outcomes below based on the formulas you gave to illustrate.  I've also switched totals mode to "expression total" rather than "sum of rows" as that also helps illustrate that totals aren't really being generated at all.

[petter]

The reason your totals doesn't calculate is that your ifs rule out any calculation on the totals row.

Thats is why you need to simplify or at least transform your calculations into pure Set Expressions or

in worst case add another If that caters for the totals row.

The totals row could be detected by something like this:

If( RowNo()=0, Sum(Column(1) , <the-regular-expression-for-the-other-rows> )

[nsm1234567]

Thanks for the response.  I've attached an example to illustrate the issue.

[nsm1234567]

How would I go about replacing my expression with set analysis only?  I did a bit of experimentation with set analysis, and got as far as the below, but this doesn't replicate the result of my expression (I've also attached a sample file a bit higher up with my original object).

sum({<[Adjusted Category]={"Current Asset","Current Liabilities"},[Account Type]={"Balance Sheet"},
Period = {$(=Max(total([Fin Period])))}>} Balance)

[petter]

I manage to get up with the sums you request and calculate the ratio... But are you sure that this is the right

numbers? Have a look at the extra visualisation I have made of the contents of both assets and liabilities and

see if it makes sense: (I also attached the modified QlikView App)