topic Re: Sum month by month in QlikView

Sum month by month

Mon, 13 Feb 2012 16:27:23 GMT

Hello,

I would like calcule in simple table, electricity production month by month in the last column (Prod. MONTH SOD) for each town.

I've created the following expression with set analysis, to calculate the last column but it does not works

=sum({1<cent_nom=, pc_date_text = {"<=$(monthend(pc_date_text))"}>} cdd_production)

The good answer for the third line of Toulouse, will be 0 + 34 570 + 34 879 = 69 449 not 34 879

Central	\|Month	\|SOD	\|Prod. TOT SOD	\|Production MONTH	\|Month Rate	\|Prod. MONTH SOD
TOULOUSE	avr. 2011	08/04/2011	261 685 kWh	0 kWh	0,00%	0 kWh
TOULOUSE	mai 2011	08/04/2011	261 685 kWh	34 570 kWh	12,71%	34 570 kWh
TOULOUSE	juin 2011	08/04/2011	261 685 kWh	34 879 kWh	12,83%	34 879 kWh
TOULOUSE	juil. 2011	08/04/2011	261 685 kWh	36 682 kWh	13,49%	36 682 kWh
TOULOUSE	août 2011	08/04/2011	261 685 kWh	38 348 kWh	14,10%	38 348 kWh
TOULOUSE	sept. 2011	08/04/2011	261 685 kWh	33 946 kWh	12,48%	33 946 kWh
TOULOUSE	oct. 2011	08/04/2011	261 685 kWh	29 115 kWh	10,71%	29 115 kWh
TOULOUSE	nov. 2011	08/04/2011	261 685 kWh	15 018 kWh	5,52%	15 018 kWh
TOULOUSE	déc. 2011	08/04/2011	261 685 kWh	12 kWh	0,00%	12 kWh
PARIS	févr. 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
PARIS	mars 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
PARIS	avr. 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
PARIS	mai 2011	28/07/2010	200 981 kWh	13 915 kWh	12,25%	13 915 kWh
PARIS	juin 2011	28/07/2010	200 981 kWh	15 090 kWh	13,28%	15 090 kWh
PARIS	juil. 2011	28/07/2010	200 981 kWh	16 333 kWh	14,38%	16 333 kWh
PARIS	août 2011	28/07/2010	200 981 kWh	16 095 kWh	14,17%	16 095 kWh
PARIS	sept. 2011	28/07/2010	200 981 kWh	14 019 kWh	12,34%	14 019 kWh
PARIS	oct. 2011	28/07/2010	200 981 kWh	12 583 kWh	11,07%	12 583 kWh
PARIS	nov. 2011	28/07/2010	200 981 kWh	6 697 kWh	5,89%	6 697 kWh
PARIS	déc. 2011	28/07/2010	200 981 kWh	671 kWh	0,59%	671 kWh
LYON	août 2011	21/06/2011	120 104 kWh	6 314 kWh	2,92%	6 314 kWh
LYON	sept. 2011	21/06/2011	120 104 kWh	25 549 kWh	11,81%	25 549 kWh
LYON	oct. 2011	21/06/2011	120 104 kWh	16 981 kWh	7,85%	16 981 kWh
LYON	nov. 2011	21/06/2011	120 104 kWh	6 250 kWh	2,89%	6 250 kWh
LYON	déc. 2011	21/06/2011	120 104 kWh	859 kWh	0,40%	859 kWh

Thanks you

Sum month by month

pover — Tue, 14 Feb 2012 00:58:18 GMT

You can't use set analysis to accumulate over a field that is also the object's dimension. In this case, you can try to inter-row function like

rangesum(above(sum(cdd_production),0,12))

or a method called a "asof" table, or in some cases, an island table.

Karl

Re: Sum month by month

Tue, 14 Feb 2012 10:22:47 GMT

Hello Karl

Thanks for your answer, but the rangesum(above()) can't works because user must be able to sort the table as they want, as you can see in the following chart.

Central	Month	SOD	Prod TOT SOD	Production MONTH	Month Rate	Prod. MONTH SOD
PARIS	févr. 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
PARIS	mars 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
TOULOUSE	avr. 2011	08/04/2011	261 685 kWh	0 kWh	0,00%	0 kWh
PARIS	avr. 2011	28/07/2010	200 981 kWh	0 kWh	0,00%	0 kWh
TOULOUSE	mai 2011	08/04/2011	261 685 kWh	34 570 kWh	12,71%	34 570 kWh
PARIS	mai 2011	28/07/2010	200 981 kWh	13 915 kWh	12,25%	83 055 kWh
TOULOUSE	juin 2011	08/04/2011	261 685 kWh	34 879 kWh	12,83%	34 879 kWh
PARIS	juin 2011	28/07/2010	200 981 kWh	15 090 kWh	13,28%	84 848 kWh
TOULOUSE	juil. 2011	08/04/2011	261 685 kWh	36 682 kWh	13,49%	36 682 kWh
PARIS	juil. 2011	28/07/2010	200 981 kWh	16 333 kWh	14,38%	89 697 kWh
TOULOUSE	août 2011	08/04/2011	261 685 kWh	38 348 kWh	14,10%	38 348 kWh
PARIS	août 2011	28/07/2010	200 981 kWh	16 095 kWh	14,17%	92 791 kWh
LYON	août 2011	21/06/2011	120 104 kWh	6 314 kWh	2,92%	153 548 kWh
TOULOUSE	sept. 2011	08/04/2011	261 685 kWh	33 946 kWh	12,48%	33 946 kWh
PARIS	sept. 2011	28/07/2010	200 981 kWh	14 019 kWh	12,34%	81 911 kWh
LYON	sept. 2011	21/06/2011	120 104 kWh	25 549 kWh	11,81%	155 425 kWh
TOULOUSE	oct. 2011	08/04/2011	261 685 kWh	29 115 kWh	10,71%	29 115 kWh
PARIS	oct. 2011	28/07/2010	200 981 kWh	12 583 kWh	11,07%	70 813 kWh
LYON	oct. 2011	21/06/2011	120 104 kWh	16 981 kWh	7,85%	129 492 kWh
TOULOUSE	nov. 2011	08/04/2011	261 685 kWh	15 018 kWh	5,52%	15 018 kWh
PARIS	nov. 2011	28/07/2010	200 981 kWh	6 697 kWh	5,89%	36 733 kWh
LYON	nov. 2011	21/06/2011	120 104 kWh	6 250 kWh	2,89%	64 698 kWh
TOULOUSE	déc. 2011	08/04/2011	261 685 kWh	12 kWh	0,00%	12 kWh
PARIS	déc. 2011	28/07/2010	200 981 kWh	671 kWh	0,59%	695 kWh
LYON	déc. 2011	21/06/2011	120 104 kWh	859 kWh	0,40%	2 237 kWh

Thanks

Sum month by month

jagan — Tue, 14 Feb 2012 10:34:39 GMT

Hi,

Try this expression

=rangesum(Above(total sum(production),0,RowNo(total)))

Regards,

Jagan.

Re: Sum month by month

Tue, 14 Feb 2012 10:50:47 GMT

Hello Jagan,

That bypass the Central dimession and perform a sum of all production's month

Regards

Re: Sum month by month

pover — Tue, 14 Feb 2012 18:06:11 GMT

You're going to have to do it with a "asof" table. I've attached an example.

Karl

Re: Sum month by month

Wed, 15 Feb 2012 10:50:01 GMT

Thanks Karl for your exemple.

I've created the following expression based on the field pc_monthyear_text_asof stored in ProductionCalendarAsOf dimension table, and the field pc_monthyear_text is in ProductionCalendar table.

=sum({1<cent_nom=, pc_monthyear_text = {"<=$(max(pc_monthyear_text_asof))"}>} cdd_production)

I still have the same result, the sum of the month and i don't use the field that is used in the dimension ( pc_date_text ). Something missing me

Regards

Re: Sum month by month

pover — Wed, 15 Feb 2012 18:19:50 GMT

If the asof table to correctly made you don't have to try to do any accumulated set analysis.

You should use the field pc_monthyear_text_asof as your dimension and then the formula for the accumlated production should be

=sum({$<cent_nom=>} cdd_production)

and the formula for the montly production should be

=sum({$<cent_nom=>} if(pc_monthyear_text_asof = pc_monthyear_text, cdd_production))

Give it a try and tell us if that works.

Karl