Calculate Rate from Salary by Month

[Anonymous]

Hello,

I'm having a bit of an issue wrapping my head around this in Qlikview. I could do this in SQL but I'd like to keep my data sources separate. What I'm trying to accomplish is calculating the rate of each resource based off that resource's salary divided by number of hours in that month. This would allow me to calculate their true cost.

My script is like this:

Fact_Temp:

LOAD

Year as [JoinYear],

Month as [JoinMonth],

Email as [JoinEmail],

[Accrual Period],

MonthName([Accrual Period]) as [Accrual Month - Year],

Year([Accrual Period]) as [Accrual Year],

[Reporting Month],

     [Client Code],

     [Client Code] as %ProjectKey,

     Email as %AccrualResourceKey,

     Role,

     Name,

     Rate as #Rate,

     [No of Hrs] as [#No of Hrs],

     Amount as #Amount,

     Amount as #Revenue,

     Exps as #Exps,

     [Est Hrs] as [#Est Hrs],

     [Est Fees] as [#Est Fees],

     [Var Hrs] as  [#Var Hrs],

     [Var Fees] as [#Var Fees],

     Totals as [#Total],

     Email

FROM

Fact_Accrual.qvd

(qvd)

where [No of Hrs] <> 0;

LEFT JOIN (Fact_Temp)

LOAD AccountNumber as %ProjectKey,

     ExpenseLineAmount as [#Vendor Expense Line Amount],

     Month as JoinMonth,

     TotalBalance as [#Vendor Total Balance],

     Year as JoinYear

FROM

IR_Fact_Quickbooks.qvd

(qvd);

LEFT JOIN

LOAD

     EMAIL as [JoinEmail],

     [CURRENT SALARY] as [#Employee Salary],

     EMAIL as [%EmployeeKey]

FROM

IR_DM_Employees.qvd

(qvd);

qualify*;

UNQUALIFY %ProjectKey, %VendorKey, %EmployeeKey, %AccrualResourceKey;

Fact:

Load *,

If(IsNull(%VendorKey),

[#Employee Salary] / [#No of Hrs],

[#Vendor Expense Line Amount]) as #Cost

Resident Fact_Temp;

drop table Fact_Temp;

However, this would give me an incorrect as it would take the total salary divided by that specific records numeric value for [#No of Hrs].

I thought i could solve this on the front end rather than the backend so I attempted a formula up front using the TOTAL function, but I think I'm missing the point there as well.

Here's an image:

The table on the left are the results. Any thoughts on how I can achieve this?

The goal is to calculate the rate dependent on the number of hours. So if I selected a resource and 2 months of data it'd grab 2 months of #No of Hrs and 2 months of a salary and calculate appropriately.

Any help would be appreciated!

Thanks,

Channing

[Anonymous · Accepted solution]

Maybe I've answered my own question. If you all see any problems let me know.

Sum(Total<Resource,Month>[#No of Hrs]

Thanks!

Channing