topic How can I create a Project status filter based on Billable Hours for a rolling 3 months? in QlikView

How can I create a Project status filter based on Billable Hours for a rolling 3 months?

Tue, 16 Aug 2016 20:55:44 GMT

I am trying to create a filter that determines the following:

If a project has greater than 0 Billable Housr within the last rolling 3 months, then the project is Active

If a project has 0 Billable Hours within the last rolling 3 months, then the project is Inactive

How would I go about this? Do I need to build an Expression that states that if Project Billable Hour is > 0, then 'Active', and if Project Billable Hour is 0, then Inactive.

I'm a pretty novice user so any advice/ direction would be helpful

Re: How can I create a Project status filter based on Billable Hours for a rolling 3 months?

Gysbert_Wassenaar — Wed, 17 Aug 2016 07:37:41 GMT

Create a field in the script when you load the billable hours:

Facts:

LOAD

ProjectID,

DateOfWork,

HoursOfWork,

If(InMonths(DateOfWork, Today(), -2), 1, 0) as InLastThreeMonths

....other fields....

FROM

...source data...

;

Then you could add a summary table with the active projects:

ActiveProjects:

LOAD DISTINCT

ProjectID,

'Active' as Status

RESIDENT

Facts

WHERE

InLastThreeMonths = 1

;