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: 
Anonymous
Not applicable

Calculate Rate from Salary by Month

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:

Salary_Rate_By_Month.PNG

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

1 Reply
Anonymous
Not applicable
Author

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