topic Re: Help with payroll allocation reporting in QlikView

Help with payroll allocation reporting

Tue, 23 Dec 2014 19:24:43 GMT

I am trying to create a report where payroll allocations are entered into the system as a date range, with a corresponding % allocated to a given project. For example:

Smith

Project x Payroll start date: 3/1/14 End Date: 2/28/15 Percentage of pay to charge Project: 50%

Project y "" "" 4/15/13 "" "" 3/29/17 "" "" 35%

Project z.....etc

The users need to see these percentages in a report, as follows, in order to check for funding gaps:

Smith

Jan 14 Feb 14 Mar 14 Apr 14 May 14 ...etc.

Project x 0 0 50% 50% 50%

Project y 35% 35% 35% 35% 35%

Project z ....

Total Payroll Schedule T 35% 35% 85% 85% 85%

Schedules frequently are frequently changed with new projects added, etc.

I am REALLY a newbie at Qlikview and programming (and yes, have a developers license).

Can anyone help me on how I would convert that range of dates into individual buckets?

Re: Help with payroll allocation reporting

Gysbert_Wassenaar — Tue, 23 Dec 2014 20:26:19 GMT

I think the best approach is the expand the intervals into individual date values. See this blog post: Creating Reference Dates for Intervals. And it looks like you want to create a flag field to mark the start (or end) date of each month: if(floor(MyDate)=floor(monthstart(MyDate)),1,0) as IsMonthStart. You can then create a straight table with Project and MyDate as dimensions and the expression sum(PayPercentage*IsMonthStart)

Re: Help with payroll allocation reporting

Tue, 23 Dec 2014 21:31:01 GMT

Oh boy, Gysbert - you may have just saved me!

I've been looking for something to convert date ranges into all dates inbetween, but just never put the search parameters in correctly.

Thanks SO much for this.

Re: Help with payroll allocation reporting

Sun, 28 Dec 2014 23:14:03 GMT

hi, Gysbert -

Dumb question here -

When I created the straight table you referenced above, with the project and mydate, using the if statement you provided, all I get is the series of 1s and 0s - I'm assuming that there is an extra step that I need to take to actually present the percentage in each month that it applies? An additional expression to make it apply that date so that I wind up with the layout:

Smith

Jan 14 Feb 14 Mar 14 Apr 14 May 14 ...etc.

Project x 0 0 50% 50% 50%

Project y 35% 35% 35% 35% 35%

Project z ....

Total Payroll Schedule T 35% 35% 85% 85% 85%

I made a sample excel spreadsheet (the actual data is loaded from an Oracle data warehouse).

Thanks!

Re: Help with payroll allocation reporting

Gysbert_Wassenaar — Mon, 29 Dec 2014 15:46:39 GMT

See attached qvw.

Re: Help with payroll allocation reporting

Tue, 30 Dec 2014 21:23:08 GMT

Thanks SO much for this.

I'm actually loading from an Oracle ODS datastore, and the script did NOT like the while statement...keeping in mind I'm a newbie - like newborn baby newbie to this stuff....would I place the while statement somewhere else in the script given the type of load I am doing?

I feel like I've learned so much doing this - thanks for helping me along. I'm soooo close to having what I need!

Re: Help with payroll allocation reporting

Gysbert_Wassenaar — Wed, 31 Dec 2014 13:24:02 GMT

When you're loading from a database you can use a preceding load so the data read from the database using a select statement are piped into the preceding load statement:

LOAD  A, B, C, B + iterno() as D
WHILE B + iterno() < C;
SELECT A, B, C FROM mydatabasetable;

Line 3 gets the data from the database. Line 1 and 2 are the preceding load that process the data that the select statement on line 3 retrieves from the database. The while clause is executed in the preceding load. See this blog post for more information: Preceding Load

Re: Help with payroll allocation reporting

Fri, 02 Jan 2015 17:31:52 GMT

FANTASTIC. Thanks again for all your help and patience. Happy 2015!

Re: Help with payroll allocation reporting

Gysbert_Wassenaar — Sat, 03 Jan 2015 08:21:00 GMT

You're welcome and Happy 2015 to you too!