topic Re: Perform calculations where expression uses dimension in QlikView

Perform calculations where expression uses dimension

nsm1234567 — Thu, 08 Jan 2015 15:06:36 GMT

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?

Re: Perform calculations where expression uses dimension

mazacini — Thu, 08 Jan 2015 17:23:50 GMT

Could you post an image of your table structure?

Joe

Re: Perform calculations where expression uses dimension

nsm1234567 — Thu, 08 Jan 2015 17:47:13 GMT

Hey There,

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

Re: Perform calculations where expression uses dimension

Thu, 08 Jan 2015 17:55:40 GMT

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

Re: Perform calculations where expression uses dimension

petter — Thu, 08 Jan 2015 19:01:38 GMT

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....

Re: Perform calculations where expression uses dimension

nsm1234567 — Thu, 08 Jan 2015 20:27:49 GMT

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.

Re: Perform calculations where expression uses dimension

petter — Thu, 08 Jan 2015 20:33:47 GMT

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> )

Re: Perform calculations where expression uses dimension

nsm1234567 — Thu, 08 Jan 2015 21:04:13 GMT

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

Re: Perform calculations where expression uses dimension

nsm1234567 — Thu, 08 Jan 2015 22:06:48 GMT

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)

Re: Perform calculations where expression uses dimension

petter — Fri, 09 Jan 2015 00:03:58 GMT

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)

Re: Perform calculations where expression uses dimension

mazacini — Fri, 09 Jan 2015 08:35:57 GMT

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