Different ratios in one table

[#1]

Hi everyone,

I have a table with different values in it from which I want to calculate ratios.

The problem is now, that sometimes I have to divide column a/coloum b and sometime column b /coloumn a

a     b       c

3     4      a/b

4     2      b/a

5     6      a/b

Has someone an idea?

Thanks a lot!

[swuehl]

And on what condition do you decide which ratio to use? Maybe just use a if() clause then?

[#3]

I attached a screenshot

So the question would be:

Can I make the formula dependent on what is written in the column bevor?

You can do that in excel with an if clause but can you do something similar in QV?

Like find(what=:....,LookAt:=Partxl???

[swuehl]

It seems to me that you decide your ratio order based on second dimension value  (Definition KPI) and or the values in column(2) / columm(3).

To check for the KPI, you can check for the dimension value using e.g. a pick / match combination, but I would suggest that you add a field to your KPI table that indicates if you need to use a/b or b/a, by using and flag, like 0 and 1. Let's call this field RatioOrder, then your expression might look like

=if( RatioOrder=1, column(2)/column(1), column(1)/column(2) )

If your ratio needs to check the actual value of e.g. Budget/... you could use something like

=if( column(2) ='NA', column(3)/column(1), column(2)/column(1) )

You can combine both to check for the dimension value / KPI and expression value.

Instead of the column() function, you might also reference the expression using its labels, like "BP 15":

=if( "Budget" ='NA', "BP 15"/"Year Value 2011", "Budget"/"Year Value 2011" )

Hope this helps,

Stefan