Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Expression for a ratio vs total

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.

     

PdVANNO DATA ORDINEMESE DATA ORDINENVALTO BE CALCULATED
006-Mer Vicenza2015feb1€ 9400,47%
006-Mer Vicenza2015apr1€ 5.8202,88%
006-Mer Vicenza2015mag2€ 2.3021,14%
006-Mer Vicenza2015giu1€ 6.2513,10%
006-Mer Vicenza2015lug2€ 6.7583,35%
006-Mer Vicenza2015ago9€ 28.87014,30%
006-Mer Vicenza2015set5€ 8.4794,20%
006-Mer Vicenza2015ott46€ 70.64734,98%
006-Mer Vicenza2015nov59€ 71.87835,59%
006-Mer VicenzaTotale 126€ 201.945100,00%
007-Mer Sarzana2014dic3€ 1.4360,22%
007-Mer Sarzana2015gen2€ 5.4390,83%
007-Mer Sarzana2015feb3€ 2.3420,36%
007-Mer Sarzana2015mar2€ 5.2800,81%
007-Mer Sarzana2015apr5€ 12.7571,95%
007-Mer Sarzana2015mag2€ 4.3300,66%
007-Mer Sarzana2015giu2€ 4.0150,62%
007-Mer Sarzana2015lug2€ 9070,14%
007-Mer Sarzana2015ago31€ 65.32210,01%
007-Mer Sarzana2015set29€ 23.9113,66%
007-Mer Sarzana2015ott153€ 232.68135,65%
007-Mer Sarzana2015nov245€ 294.18045,08%
007-Mer SarzanaTotale 479€ 652.600100,00%

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

Thanks to everybody who can help me!!!

bye

W

Tags (1)
1 Solution

Accepted Solutions
Marcio_Campestrini
Valued Contributor

Re: Expression for a ratio vs total

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

Márcio Rodrigo Campestrini
4 Replies
Marcio_Campestrini
Valued Contributor

Re: Expression for a ratio vs total

Hi

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

Márcio

Márcio Rodrigo Campestrini
Not applicable

Re: Expression for a ratio vs total

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

W

Marcio_Campestrini
Valued Contributor

Re: Expression for a ratio vs total

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

Márcio Rodrigo Campestrini
Not applicable

Re: Expression for a ratio vs total

Thanks Marcio!