Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
W
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
Hi
Try this app. I just don't know how to put the total for each year to 100%.
Márcio
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
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
Thanks Marcio!