Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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

1 Reply

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

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

     ;