Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tschullo
Creator III
Creator III

Budget Hrs by Country Vs Actuals By Country

have a dashboard that contains employee (resource) timesheets, reource data, project data and project busget hrs data.

The budget was initially at the project level, but I was recently given an additional table that breaks the budget down by resource country and project.

budget1.JPG

 

The new table's "Country" column corresponds to Resource.Location column.

The requirement I am trying to fulfill is to make Location a filter and be able to display the actual hrs vs budget hrs by country.

So, if I don't select a country I see the actuals vs budget by Project.

Project IDSum Rsc HrsSum BudgetHrsByCtry
A122290180
A1234200210
A125420150

If I select US as Location, the Actual Hrs for US resources are displayed against US Budget Hrs.

Project IDSum Rsc HrsSum BudgetHrsByCtry
A12224070
A123411080

What changes do I have to make to my model to make this happen?

Here is the script:

Timesheets:
load * inline [
TS_ID, Rsc_ID, Week,  Project_ID, Hrs
1000, 101, 1,  A1234,  40
1001, 101, 2,  A1234,  40
1002, 101, 3,  A1222,  40
1003, 101, 4,  A1234,  30
1010, 102, 1,  A1254,  20
1011, 102, 2,  A1234,  10
1012, 102, 3,  A1222,  40
1013, 102, 4,  A1234,  40
1020, 103, 1,  A1234,  10
1021, 103, 2,  A1234,  10
1022, 103, 3,  A1222,  10
1023, 103, 4,  A1234,  20
] ;

Resources:
load * inline [
Rsc_ID, Name, Location
101, Bob, US
102, Rex, UK
103, Lee, HK
] ;

Projects:
load * inline [
Project_ID, ProjectName, BudgetHrs
A1234,  ACME,   210
A1222,  NIMBY,   180
A1254,  HERSHEY,  150
] ;

CtryBusget:
load * inline [
Project_ID, Country, BudgetHrsByCtry
A1234,  US,   80
A1222,  US,   70
A1254,  US,   60
A1234,  UK,   80
A1222,  UK,   50
A1254,  UK,   50
A1234,  HK,   50
A1222,  HK,   50
A1254,  HK,   50
] ;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe

=Sum({<Location = p(Country)>} Hrs)

to show only the Hrs for resources with a Location that is possible in field Country values.

View solution in original post

5 Replies
swuehl
MVP
MVP

If you just want to compare the data on a Country / Project level, you can prepare data in the script by adding this to your above script snippet:

TMP1:

LOAD Project_ID, Rsc_ID, Sum(Hrs) as SummedHrs

Resident Timesheets

GROUP BY Project_ID, Rsc_ID;

LEFT JOIN

LOAD Rsc_ID, Location

Resident Resources;

LEFT JOIN (CtryBusget)

LOAD Project_ID, Location as Country, Sum(SummedHrs) as ActualHrsByCtry

Resident TMP1

GROUP BY Project_ID, Location;

tschullo
Creator III
Creator III
Author

Yes, that would work for my simplistic example model, but what I really want is for a link to exist so that If I choose to filter timesheets by month or resources by team, I would get an actual hrs count that reflects the effort for a given month or year.

swuehl
MVP
MVP

Maybe

=Sum({<Location = p(Country)>} Hrs)

to show only the Hrs for resources with a Location that is possible in field Country values.

tschullo
Creator III
Creator III
Author

Thanks Stefan. Very nice solution.  Can you point me to some further reading on that syntax?

swuehl
MVP
MVP

I guess you are referring to the part between the curly brackets:

This is called Set Expression or Set Analysis, to start with:

A Primer on Set Analysis

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalys...

Set Analysis: syntaxes, examples