topic Re: multiplying values by percentage in QlikView

multiplying values by percentage

Wed, 11 Jun 2014 19:26:53 GMT

I'm trying to figure out how I can multiply a value by different set of percentages which are in the tables. Can somebody help?

Re: multiplying values by percentage

swuehl — Wed, 11 Jun 2014 19:30:10 GMT

Please provide some more details about these tables and where your value comes from.

Basically, QV can perform numeric operations, so multiplying percentages with values are simple:

=3 * 0.11

or formatted as you like, e.g.

=num(3*0.11, '00%')

Re: multiplying values by percentage

Wed, 11 Jun 2014 19:59:54 GMT

Basically, I have a table of percentages like

Month, Group, ratio1, ratio2

Jan, A, 50%, 60%

Jan, B, 50%, 40%

Feb, A, 55%, 70%

Feb, B, 45%, 30%

First, I want a selection box that will select between what ratio to use (ratio1 or ratio2). Then, I want to use the ratio value based on the set of ratio selected in an expression.

I hope that helps to explain what I'm trying to do.

Re: multiplying values by percentage

swuehl — Wed, 11 Jun 2014 20:19:58 GMT

And your values are in a fact table linked by Month and Group? Or just Group?

QV should be able to interpret your percentages correctly when read in, if not (check that the values are actually numbers, e.g. by checking the fields in table viewer or formatting a list box of rationX as number with decimals) use num#() function to interpret the values explicitely.

Then creating an expression should be simple, but maybe the complexity arise from the way your data is linked. So please post a more complete view on your data (Where are the values located and how are they linked to the above table).

Re: multiplying values by percentage

Wed, 11 Jun 2014 20:29:12 GMT

The expression is working but I'm trying to figure out a selection box to point which ratio to use whether it's ratio1 or ratio2.

Re: multiplying values by percentage

Clever_Anjos — Wed, 11 Jun 2014 21:03:31 GMT

1) Create a small inline table with two values "Ratio1" and "Ratio2"

2) Add a listbox to your application

3) Select one value

4) Configure as "Always one selected" and "Windows Checkbox" style

5) Use an "if"

sum(Value*if(newfield='Ratio1',Ratio1,Ratio2))

Re: Re: multiplying values by percentage

Clever_Anjos — Wed, 11 Jun 2014 21:08:26 GMT

PFA a small example

Re: multiplying values by percentage

Wed, 11 Jun 2014 21:18:18 GMT

Hi,

If you want to use ratio1 * ratio2 ... for example un a straigth table you can try an expresion like this:

(PurgeChar(ratio1,'%'))*Num(PurgeChar(ratio2,'%'))

PurgeChar function erase % simbol and you can use ratio1 and ratio2 as a number.

Best regards.

Re: multiplying values by percentage

Wed, 11 Jun 2014 21:29:45 GMT

How do you write #5 if there's more than two ratios? What is the 'Value'?

Re: multiplying values by percentage

Wed, 11 Jun 2014 21:37:52 GMT

I'm not trying to multiply the ratios. I have multiple sets of ratios and I'm trying to use it on a formula based on the selected set of ratios.

Re: multiplying values by percentage

MarcoWedel — Wed, 11 Jun 2014 21:43:38 GMT

Hi Dennis,

maybe using a crosstable load of your ratio table?:

table1:

CrossTable (ratioType, ratio, 2)

LOAD * INLINE [

Month, Group, ratio1, ratio2

Jan, A, 50%, 60%

Jan, B, 50%, 40%

Feb, A, 55%, 70%

Feb, B, 45%, 30%

];

table2:

LOAD * INLINE [

Month, Group, value1, value2

Jan, A, 1234, 4321

Jan, B, 2345, 5432

Feb, A, 3456, 6543

Feb, B, 4567, 7654

];

hope this helps

regards

Marco

Re: multiplying values by percentage

Wed, 11 Jun 2014 21:50:10 GMT

Hi,

Try those expresions:

Num(

value1 * (Num(PurgeChar(ratio1,'%'))/100)

,'#,##.0.0%; -#,##.0.0%')

and

Num(

value1 * (Num(PurgeChar(ratio2,'%'))/100)

,'#,##.0.0%; -#,##.0.0%')

best regards.

Re: Re: multiplying values by percentage

MarcoWedel — Wed, 11 Jun 2014 21:55:15 GMT

or with some more ratios:

table1:

CrossTable (ratioType, ratio, 2)

LOAD * INLINE [

Month, Group, ratio1, ratio2, ratio3, ratio4

Jan, A, 50%, 60%, 45%, 65%

Jan, B, 50%, 40%, 70%, 85%

Feb, A, 55%, 70%, 15%, 35%

Feb, B, 45%, 30%, 25%, 90%

];

regards

Marco

Re: multiplying values by percentage

Wed, 11 Jun 2014 22:17:06 GMT

This looks like what I'm looking for. Let me try it. Is the '2' the number of ratios defined in

CrossTable (ratioType, ratio, 2)

Re: multiplying values by percentage

MarcoWedel — Wed, 11 Jun 2014 22:31:12 GMT

no, this '2' defines the number of columns before the ratioX columns, here: Month and Group.

regards

Marco

Re: multiplying values by percentage

Thu, 12 Jun 2014 00:39:02 GMT

Thanks, it worked.