topic Re: Expression for a ratio vs total in QlikView

Expression for a ratio vs total

Thu, 10 Dec 2015 09:57:12 GMT

Hi guys,

I'm quite new!

One question (I hope easy for you):

I have loaded into QLIK a table like the one below (for your convenience I've attached the completed Excel file).

I'd like to have in Qlik a column like the one named "TO BE CALCULATED" where I'd like to have the following formula:

VAL (row level) /TOTAL VAL (for this specific "PdV").

Example for the 1st row: 940 / 201945 = 0,47%

If you want to see the formula in Excel please open the attachment.

PdV	ANNO DATA ORDINE	MESE DATA ORDINE	N	VAL	TO BE CALCULATED
006-Mer Vicenza	2015	feb	1	€ 940	0,47%
006-Mer Vicenza	2015	apr	1	€ 5.820	2,88%
006-Mer Vicenza	2015	mag	2	€ 2.302	1,14%
006-Mer Vicenza	2015	giu	1	€ 6.251	3,10%
006-Mer Vicenza	2015	lug	2	€ 6.758	3,35%
006-Mer Vicenza	2015	ago	9	€ 28.870	14,30%
006-Mer Vicenza	2015	set	5	€ 8.479	4,20%
006-Mer Vicenza	2015	ott	46	€ 70.647	34,98%
006-Mer Vicenza	2015	nov	59	€ 71.878	35,59%
006-Mer Vicenza	Totale		126	€ 201.945	100,00%
007-Mer Sarzana	2014	dic	3	€ 1.436	0,22%
007-Mer Sarzana	2015	gen	2	€ 5.439	0,83%
007-Mer Sarzana	2015	feb	3	€ 2.342	0,36%
007-Mer Sarzana	2015	mar	2	€ 5.280	0,81%
007-Mer Sarzana	2015	apr	5	€ 12.757	1,95%
007-Mer Sarzana	2015	mag	2	€ 4.330	0,66%
007-Mer Sarzana	2015	giu	2	€ 4.015	0,62%
007-Mer Sarzana	2015	lug	2	€ 907	0,14%
007-Mer Sarzana	2015	ago	31	€ 65.322	10,01%
007-Mer Sarzana	2015	set	29	€ 23.911	3,66%
007-Mer Sarzana	2015	ott	153	€ 232.681	35,65%
007-Mer Sarzana	2015	nov	245	€ 294.180	45,08%
007-Mer Sarzana	Totale		479	€ 652.600	100,00%

What's the expression I have to setup in QLIK to have back this result??

Thanks to everybody who can help me!!!

bye

Re: Expression for a ratio vs total

Marcio_Campestrini — Thu, 10 Dec 2015 12:42:59 GMT

Try this app. I just don't know how to put the total for each year to 100%.

Márcio

Re: Expression for a ratio vs total

Mon, 14 Dec 2015 14:13:07 GMT

hi Marcio,

thanks for your reply.

One favour... since I have the free version of qlik and cannot open a file made by any other PC, could you be so kind to send me the expression you used.

one more remark: I don't want the total per year, I want each record of a specific "PdV" will be divided by the "total of Val" of this "PdV" .

For example for the Pdv = "007-Mer Sarzana" the result 0,22% will be calculated by the division 1.436/652.600.

In fact 1436 is the value of column "val" for this record, while 652.600 is the total of "Val column" values of the "PdV" called "007-Mer Sarzana".

Thanks

Regards

Re: Expression for a ratio vs total

Marcio_Campestrini — Mon, 14 Dec 2015 16:35:51 GMT

Hi Wal gno

The expression I used is Sum(VAL) / Sum(Total<PdV> VAL).

Since you don't want to totalize the year, I created a Straight Table Chart.

Marcio

Re: Expression for a ratio vs total

Tue, 29 Dec 2015 09:43:33 GMT

Thanks Marcio!