topic Sum records based on a condition in QlikView

Sum records based on a condition

Wed, 12 Oct 2011 13:53:28 GMT

Hi!

I would like to Sum the [# sent] and [# sent on time] for each country when I select specific months. For the below example, if I select Jan-11, Feb-11, and Mar-11, I need to have the total for each country according to the months selected.

Original table is

Country	Month	# sent	# sent on time	% on time
Country A	Jan-11	4	4	100.00
Country A	Feb-11	5	4	80.00
Country A	Mar-11	3	3	100.00
Country B	Jan-11	7	7	100.00
Country B	Feb-11	2	2	100.00
Country B	Mar-11	8	7	87.50
Country C	Jan-11	5	5	100.00
Country C	Feb-11	10	10	100.00
Country C	Mar-11	1	1	100.00
Country D	Jan-11	4	4	100.00
Country D	Feb-11	9	8	88.89
Country D	Mar-11	7	7	100.00

I need the totals to figure in a table as per the below. the percentage is recalculated based on the new numbers.

	# sent	# sent on time	% on time
Country A	12	11	91.67
Country B	17	16	94.12
Country C	16	16	100.00
Country D	20	19	95.00

I would highly appreciate your help,

Regards,
Julie

Sum records based on a condition

Wed, 12 Oct 2011 13:57:30 GMT

Just create a straight or Pivot table with the only dimension= Country

sum(sent#)

Sum(sentontime#)

Re: Sum records based on a condition

its_anandrjs — Wed, 12 Oct 2011 14:02:19 GMT

Hi,

Use pivot table and create a chart like add sum of values

See the attached sample file

Rgds

Anand

Re: Sum records based on a condition

SunilChauhan — Wed, 12 Oct 2011 14:04:38 GMT

here is the exact result you want

see the attached file

Re: Sum records based on a condition

its_anandrjs — Wed, 12 Oct 2011 14:07:13 GMT

Hi,

Use

Dimension -> Country

Expression1 -> sum([# sent])

Expression2 -> Sum([# sent on time])

Expression3 -> Sum([# sent on time]) / sum([# sent]) and set decimal place 2 and fixed to 2 and show in % in chart properties

See the update file with percentage, see the pivot chart.

Rgds

Anand

Sum records based on a condition

Thu, 13 Oct 2011 06:58:04 GMT

Thank you .

It was very helpful!!!!

Re: Sum records based on a condition

SunilChauhan — Thu, 13 Oct 2011 08:18:17 GMT

hello julierizkallah ,

did you check my post ,is it wrong ?

Re: Sum records based on a condition

Thu, 13 Oct 2011 08:29:06 GMT

At the contrary. It was very correct. But i could only post Correct Answer one time

Sum records based on a condition

SunilChauhan — Thu, 13 Oct 2011 08:38:11 GMT

i am not bother but you should check on first come first serve basis .

its deserves right answer.

Re: Sum records based on a condition

Thu, 13 Oct 2011 08:44:19 GMT

It's my first time that i post there so i thought i could mark all correct answers. Next time i will do for sure