Count next N months

Mon, 05 Sep 2011 11:56:01 GMT

Hi,

I am a relative newbe to QV so any help in pointing me in the correct direction will be welcome.

I have the following business requirement. For each project I need to count then number of issues raised after the go-live date. This seems straight forward however the data model I am working with is as follows:

Projects Table

- Project Number

- Client Name

- Go-live Date

Client Table

- Client Name

Issue Table

- Issue Number

- Client Name

Therefore Issue are raised against the client rather than the project. Each project has a specific go-live date. I then need to know the number of issues raised in say the next 6 months. In this scenario there is only ever one project per client.

An example of the output could be something like:

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6
Project Name	Go-Live	Month 1	Month 2	Month 3	Month 4
Project A	28 Aug 2010	87	60	40	10
Project B	14 Dec 2000	10	10	10	10
Project C	17 Jan 2005	70	100	70	10

Any help would be appreciated.

Count next N months

swuehl — Mon, 05 Sep 2011 12:13:38 GMT

Damian,

If you have a 1:1 relation between Customer and Project, I think you should be fine with just loading the tables in as is. QV will link your Issue Number via Client Name to the project table.

But I think you need a Issue Date, don't you? Or how to you kind of bucket your issues into the months after going live?

If you have an issue date, I think an expression as needed could look like (without testing):

=count( if (addmonths(Go_Live_Date,1) > Issue_Date and Issue_Date >= Go_Live_Date, Issue_Number))

and for the next month:

=count( if (addmonths(Go_Live_Date,2) > Issue_Date and Issue_Date >= addmonths(Go_Live_Date,1), Issue_Number))

Hope this helps,

Stefan

topic Count next N months in QlikView

Count next N months

Count next N months