Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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

     ;


talk is cheap, supply exceeds demand