Help with payroll allocation reporting

[#1]

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?

[Gysbert_Wassenaar]

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)

[#3]

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.

[#4]

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!

[Gysbert_Wassenaar]

See attached qvw.

[#6]

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!

[Gysbert_Wassenaar · Accepted solution]

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

[#8]

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

[Gysbert_Wassenaar]

You're welcome and Happy 2015 to you too!