Aggregat value between start and end dates

[atafsson]

Hi all,

I have a problem that looks like this:

Example

Table:

ServiceID        StartDate          EndDate          Amount

1                    20110101         20380101           1250

2                    20130405          20140405          1000

3                    20090213          20380213          1320

4                    20140901          20150901          1400

5                    20140115          20390119          1000

I want to present the sum of all ServiceID that are active for each month in 2013, wich would be ServiceID = 1, 2, and 3, in a pivot table.

I've tried to use a if-statement like: if(date >=StartDate and date <=EndDate, sum(amount))

But it only puts the value on the StartDate Year month, obviously.

How to create a expression to aggregate all Amounts from earlier periods?

If I generate dates for each serviceID between startdate and enddate I get around 300 000 000 million rows, so I would like to avoid that.

The result would look like this:

ServiceID     Jan      feb        mar     apr         may     jun     jul      aug      sep     oct       nov      dec

1               1250     1250      1250    1250     1250     1250  1250   1250    1250    1250    1250    1250

2               0             0           0       1000     1000     1000   1000   1000    1000   1000    1000    1000

3              1320      1320      1320    1320      1320    1320    1320   1320   1320    1320    1320    1320

Sum          2570     2570       2570    3570     3570     3570     3570  3570   3570    3570    3570    3570

Regards Axel

[Gysbert_Wassenaar]

See attached qvw

[atafsson]

Thanks Gysbert!

I don't get it to work. My problem is that the ServiceID can have more than one EndDate.

ServiceID        StartDate          EndDate          Amount

1                    20110101         20380101           1250

2                    20090505         20110505          1250

2                    20130405          20140405          1000

3                    20090213          20380213          1320

4                    20140901          20150901          1400

5                    20140115          20390119          1000

it's probably a simple solution, but I don't get inte to work.

I use a more advanced calendar aswell, see attached file.

//AG

[atafsson]

Would'nt it be possible to use a aggr()-function?

something ike this:

aggr(sum(Amount), ServiceID, Month)

//AG