Sum month by month

[#1]

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

[pover]

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

[#3]

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

[jagan]

Hi,

Try this expression

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

Regards,

Jagan.

[#5]

Hello Jagan,

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

Regards

[pover]

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

Karl

[#7]

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

[pover]

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