# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

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.

Tags (2)
1 Solution

Accepted Solutions
Not applicable

## Re: How to calculate monthly costs based on year costs

Maybe like

EmployeeID,

YearlySalary / 12 as MonthlySalary,

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

RESIDENT YourSalaryTable

WHILE IterNo() <=12;

2 Replies
Not applicable

## Re: How to calculate monthly costs based on year costs

Maybe like

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.

Community Browser