Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to calculate monthly costs based on year costs

Dear Qlikviewers,

I'm struggling with a pretty common and simple task/problem. I hope you guys could help me solve it.

I have an Excel with the budgets for all the salaries per employee (per row), this is a yearly budget. Let's say € 120.000

I want to get € 10.000 per month in my QV table...

How can I load this Excel in a way that I get the salaries on a monthly base, including the month timestamp (e.g. 2016-01-01)

Columns in the sheet: EmployeeID, EmployeeName, DepartmentName, DepartmentID, FTE%, Salary

Could I do this with a crosstable? Or should I do something in the load script?

For now I simply do 'Salary /12    as Monthly_Salary' but I have no link with a datefield or calendar

Could something like this be the solution?:

'Salary /12    as [2016-01-01]'

'Salary /12    as [2016-02-01]'

'Salary /12    as [2016-03-01]'  etc

We want to do this to compare our budget(s) with the actual spendings per month. Which comes from a SAP DB.

Thanks in advance for all your help!

Tags (2)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: How to calculate monthly costs based on year costs

Maybe like

LOAD

     EmployeeID,

     YearlySalary / 12 as MonthlySalary,

     MonthName(Makedate(Year, Iterno() )) as MonthYear

RESIDENT YourSalaryTable

WHILE IterNo() <=12;

2 Replies
swuehl
Not applicable

Re: How to calculate monthly costs based on year costs

Maybe like

LOAD

     EmployeeID,

     YearlySalary / 12 as MonthlySalary,

     MonthName(Makedate(Year, Iterno() )) as MonthYear

RESIDENT YourSalaryTable

WHILE IterNo() <=12;

Not applicable

Re: How to calculate monthly costs based on year costs

Thanks Swuehl!

That kind of worked. I'm not familiar with the IterNo()-function.

I will check my 'QlikView 11 for developers' book for some training material.

I can now make a beautiful chart in which we can see our forecasted budget versus actual costs.