topic Re: How to calculate a ratio in QlikView

How to calculate a ratio

Thu, 05 Nov 2015 23:36:08 GMT

Hello everybody, new using qlikview i cant find solution to my problem:

here a little description:

i have the follow cross table in Qlikview and i need to calculate column 5 (Ratio Sales / Chimical Activity Sales).

Country1	City	Sales	Chimical Activity Sales	Ratio Sales / Chimical Activity Sales
Irland	Dublin	100	80	100/980
Japan	Tokio	200	100	200/980
	Osaka	450	400	450/980
USA	NY	300	100	300/980
	Boston	500	300	500/980
TOTAL		1 550	980	1550/980

i was trying to use TOTAL fonction but is not working

Sum([Sales / sum(TOTAL Chimical Activity Sales]

how could i do to fixe the denominator "980" ?...

Thanks

Re: How to calculate a ratio

miguelbraga — Fri, 06 Nov 2015 12:08:49 GMT

Hi Teddy,

I think your expression is wrong:

What you have:

Sum([Sales / Sum (TOTAL Chimical Activity Sales]

What you should have:

Sum([Sales]) / Sum ([Chimical Activity Sales])

Enable totals in Country 1 and this should do the trick

Regards,

Re: How to calculate a ratio

Anonymous — Fri, 06 Nov 2015 12:14:25 GMT

if Chimical Activity Sales is fixed like 980

then you can try:

Sum([Sales])/980 in expression For Ratio

Then in number format you can set this expression with Ticked Show in % with 2 decimal

Re: How to calculate a ratio

Anonymous — Fri, 06 Nov 2015 12:16:40 GMT

Also you can defined a variable with:

Ctrl+Alt+V

like VChemicalSales=980

then

Expression: sum(Sales)/$(VChemicalSales)

Hope this will help!!

Re: How to calculate a ratio

Kushal_Chawda — Fri, 06 Nov 2015 12:24:54 GMT

try,

sum(Sales)/ Sum( Total [Chimical Activity Sales])

Re: How to calculate a ratio

sauliusr — Fri, 06 Nov 2015 12:25:45 GMT

You expression should be:

Sum([Sales]) / Sum (TOTAL [Chimical Activity Sales])

Re: How to calculate a ratio

Fri, 06 Nov 2015 14:26:22 GMT

Hi thanks but in my cross table

When I’m trying Sum([Sales]) / Sum ([Chimical Activity Sales])

Sum([Sales]) / Sum (TOTAL [Chimical Activity Sales])

He is giving to me for the first row (100/80), second row (200/100) and so on,

but the ratio that I need is 100/980 for the first row, 200/980 for second and so on.

In my exemple 980 is a calculated value is not fixed amount, is the total of column “Chimical Activity Sales”

Regards

Tedtris.

Re: How to calculate a ratio

Fri, 06 Nov 2015 14:27:08 GMT

Hi thanks but in my cross table

980 is a calculated value is not fixed amount, is the total of column “Chimical Activity Sales”

Regards

Tedtris.

Re: How to calculate a ratio

Fri, 06 Nov 2015 14:29:52 GMT

Hi thanks but in my exemple 980 is a calculated value is not fixed amount, is the total of column “Chimical Activity Sales”.

Regards

Teddy

Re: How to calculate a ratio

Fri, 06 Nov 2015 14:31:12 GMT

Hi thanks but in my exemple 980 is a calculated value is not fixed amount, is the total of column “Chimical Activity Sales”.

Regards

Tetris

Re: How to calculate a ratio

miguelbraga — Fri, 06 Nov 2015 15:04:06 GMT

If so, then only change the variable given by Balraj to this:

Sum (TOTAL [Chimical Activity Sales])

This way you get what I require

Re: How to calculate a ratio

miguelbraga — Fri, 06 Nov 2015 15:14:54 GMT

Hey Teddy,

What is your expected results? Maybe then we can help you

Regards,

Re: How to calculate a ratio

Kushal_Chawda — Fri, 06 Nov 2015 15:43:26 GMT

Create Variable on front end

vTotalSales = sum(aggr(sum([Chimical Activity Sales]),Country1,City))

Now write expression

=sum(Sales) / $(vTotalSales)

Re: How to calculate a ratio

Fri, 06 Nov 2015 23:23:14 GMT

Hi Kush i think that using aggr is a good idea but i dont know how to use the syntax, is not working.

in attached file you will find the qvw file

many thanks

Tetris