2 Replies Latest reply: Jul 29, 2016 9:32 AM by Koos Snoeks RSS

    How to calculate monthly costs based on year costs

    Koos Snoeks

      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!