conditional sum

[HRZ999]

Hi,

I got a db with this scenario :

DATE NAME VALUE

02/09 PIPPO 5

05/09 PLUTO 3

10/10 PAPERINO 9

11/10 PLUTO 2

and I am searching a way to calculate, with a calculation field, the total VALUE of one NAME in a period (like in September).

Can anyone help me ?

Thanks in advance, I'll appreciate.

Edited November 3, 2007 by Guest

[moonunit]

Hi there,

Is the calc result you want a random query or do you need to do something specific with the result?

Assuming all you want to do is look at it, my idea would probably be like this...

[color:green]Create a DateRange calc field

Date ( Month ( date ) ; 1 ; Year ( date ) )

this abstracts your dated entries to differentiate them by month and year. This calc field should be placed in the sub-summary part.

[color:green]Create a Sub-Summary part, when sorted by DateRange, in your layout

Then...

[color:green]Create another calc field called, say TotalPippo:

Case ( name = "pippo"; Sum ( value ); )

This doesn't need to be placed on your layout, but is used by the next calc.

[color:green]Create a summary field called TotalPippo2 - the summary is the total of TotalPippo.

This should be placed in the sub-summary part alongside the daterange field.

Sort.

Switch to preview mode...

Month and Pippo total will appear.

Repeat the last two steps to create Totals for Pluto, etc.

I'm sure others will come up with snappier ways of doing this, but this works albeit that you have to enter preview mode to see it in action...