topic Aggregat value between start and end dates in QlikView

Aggregat value between start and end dates

atafsson — Wed, 26 Jun 2013 08:55:41 GMT

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 — Wed, 26 Jun 2013 09:30:35 GMT

See attached qvw

atafsson — Wed, 26 Jun 2013 14:27:58 GMT

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 — Wed, 26 Jun 2013 14:48:23 GMT

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

something ike this:

aggr(sum(Amount), ServiceID, Month)

//AG