topic Calculation Help in QlikView

Calculation Help

cbaqir — Thu, 13 Nov 2014 21:28:18 GMT

I don't know why I am having such difficulty with this but I thought I would finally ask for some help as I am sure there is a simple solution. I am struggling with how to use sum(total...) and sum(all...) and aggr so maybe that is the key.

ADJ_HRS = an individual's capacity for the month (hours) from a pool (they could be in multiple pools for a split % of their time)

ALLOC_HRS = # hours an individual is allocated to a project from a pool (they could be in multiple pools for a split % of their time)

ACT_HRS = # of hours an individual booked on their timesheet

MONTH_CAP = Total # of working hours in the month

If the data was all at the same granularity, I would think a simple FTE Capacity Calculation would be sum(ADJ_HRS)/sum(MONTH_CAP). Unfortunately, this is not the case.

How can I get the expression to sum up the selected user's ADJ_CAP across multiple rows while only taking one instance of the MONTH_CAP per month selected? If there are several people selected vs 1, the sum(month_cap) should not change if the time period remains the same.

Pool	FTE Capacity	Allocated FTEs	Actual FTEs
A	sum(ADJ_HRS)/sum(MONTH_CAP)	sum(ALLOC_HRS)/sum(MONTH_CAP)	sum(ACT_HRS)/sum(MONTH_CAP)

TIA,

Cassandra

Re: Calculation Help

Gysbert_Wassenaar — Fri, 14 Nov 2014 09:12:41 GMT

Without some real data to play around with, I can only make a guess. Perhaps sum(ADJ_HRS)/only(MONTH_CAP) will work. If not, please post an example document that demonstrates the problem.

Re: Calculation Help

cbaqir — Fri, 14 Nov 2014 19:52:42 GMT

I'm not sure how to post a QVW given my data sources... but can you give me an explanation on only()?

I'm trying something new...

I have a calendar table that has date and MONTH_CAP:

10/01/2014 184

10/02/2014 184

... 10/31/2014 184

11/01/2014 144

... 11/30/2014 144

In my FACT table, I have several rows that show DATE, ALLOC_HRS, ADJ_CAP and ACT_HRS. These hours would need to be summed up:

DATE, ALLOC_HRS, ADJ_CAP and ACT_HRS

Person A 10/01/2014, 100, 176, 110

Person A 11/01/2014, 100, 136, 120

Output for Person A for October:

FTE Capacity: sum(ADJ_CAP)/only(MONTH_CAP)? 176/184= .96 FTE

FTE Alloc: sum(ALLOC_HRS)/only(MONTH_CAP)? 100/184= .54 FTE

FTE Actual: sum(ACT_HRS)/only(MONTH_CAP)? 110/184= .6 FTE

Output for Person A for October and November:

FTE Capacity: sum(ADJ_CAP)/sum(MONTH_CAP)? 312/328 = .96 FTE

FTE Alloc: 200/328 = .61 FTE

FTE Actual: 230/328 = .7 FTE

Re: Calculation Help

Gysbert_Wassenaar — Sat, 15 Nov 2014 08:53:41 GMT

See this document: Preparing examples for Upload - Reduction and Data Scrambling