Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Aggregat value between start and end dates

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

3 Replies
MVP & Luminary
MVP & Luminary

See attached qvw


talk is cheap, supply exceeds demand
Creator
Creator

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

Creator
Creator

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

something ike this:

aggr(sum(Amount), ServiceID, Month)

//AG