Sum of value for previous set of dates

[Anonymous]

Hi Friends,

I have a transaction table in the following format.

Section               Month               Date                         Type                    Crop

X                         1               01/01/2011               Normal               100

X                         1               02/01/2011       Normal               200

X                         1               31/01/2011               Normal               300

X                         2               01/02/2011               Normal               400

X                         2               02/02/2011               Normal               300

X                         2               28/02/2011               Normal               100

From this transaction table I want to calculate total crop For The Month and To Date

wise.It must be done trough pivot table.The output will be

Section          Month  Date                                   Type          Crop     For The Month     To Date

X                    1               01/01/2011               Normal               100               100                                        100

X                    1               02/01/2011               Normal               200               300                                        300

X                    1               31/01/2011               Normal               300               600                       600

X                    2               01/02/2011               Normal               400               400                                        1000

X                    2               02/02/2011               Normal               300               700                                        1300

X                    2               28/02/2011               Normal               100               800                                        1400

Please share your ideas to solve the problem.

Regards & Thanks

Chandan

[Miguel_Angel_Baeyens]

Hello Chandan,

To get the "For the Month" expression you can use the following

Sum(TOTAL <Month> Crop)

Depending on the chart you are using, to get the "To Date" expression you can use the following

RangeSum(Above(Sum(Crop), 0, RowNo()))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Anonymous]

Hello Miguel,

Thank you for your answer.To Date is ok for a month but when Month gets changed the value also gets initialized.

The value should not be initialized,it will keep roolup untill a new Section comes.If you look into the Output the 'To Date values' are not being changed.

regards,

chandan