topic Expression in a pivot table help in QlikView

Expression in a pivot table help

m4u — Thu, 14 Jan 2010 12:03:44 GMT

Hello

I have two tables:

employee timesheet with employee, role, date , date year, date month and hours worked on that date

standard hours - date and standard hours for that date

I want to show both figures in a pivot table pivoted by role, employee, year, month - whith each cell showing me the amount of hours the employee worked that month and the number of standard hours for that month (basically the standard hours figure is the same for all employees)

I have Sum(HoursWorked) to show the hours worked, and SUM(StandardHours) to show the standard hours

So far so good

The problem begings on those months where a certain employee had no records at all. For some reason, for those months the standard hours figure is showing zero as well, even though it should show the figure from the standard hours table, which exists

What am I doing wrong, or what should I change to make it work?

Expression in a pivot table help

Thu, 14 Jan 2010 12:37:50 GMT

Can you post an example of your app so I can take a look?

Expression in a pivot table help

m4u — Thu, 14 Jan 2010 16:08:21 GMT

The app is quite big so I dont know how to put it here - but the scenario is quite simple and happens in all charts - for instance, straight table:

Ill try to rephrase

Table 1 - Date, HoursStandard (single record per date)

Table2 - Date, Employee, HoursWorked (multiple records per single date)

I want to display a chart with 4 fields - employee, date, SUM(HoursWorked), SUM(HoursStandard).

The thing is, that whenever I dont have a record on a certain date for a certain employee in Table2, I still want to show that line in my straight table :

Employee A, 12/1/2010, HoursWorked=0, StandardHours=9

For some reason, if HoursWorked=0, the record doesn't show at all..

Expression in a pivot table help

Thu, 14 Jan 2010 16:24:06 GMT

Take a look at the attached, I did nothing special so this was why I was asking for a mock-up, because I can't see what could have gone wrong.

Unless of course you tell me that the attached is not what you are after.

Let me know,

Expression in a pivot table help

m4u — Thu, 14 Jan 2010 16:30:49 GMT

In the attached file - A.Jones appears only in 5 lines - for the dates he has data. I want him to appear in all 12 lines of the Standard table.. sort of right join, with 0 next to his hours..

Expression in a pivot table help

Fri, 15 Jan 2010 10:18:32 GMT

In that case you will probably need to create a single table from your two tables, probably using a full join so that your single table holds dates and employees. What is the source of your data, if it is SQL then I might be able to help with the query?

Cheers,

Expression in a pivot table help

m4u — Sun, 17 Jan 2010 17:22:12 GMT

It is SQL Server, and I know how to create a query to bring all data as a single table on the db level, but I was wondering how this can be acheived using the existing data model. This is due to the fact that the model itself holds several other tables associated with those ones, and logically this is the right table structure.. so I am wondering whether there is some way to solve this on a display level..

Thanks for your help

Expression in a pivot table help

Mon, 18 Jan 2010 09:47:15 GMT

OK, so the way I did it in a straight table was to add the following expression:

=Sum(Total HoursWorked)

Obviously you don't want that expression so I hid the column in the presentation tab.

This seems to work for a straight table, not sure about how to do the same thing with other chart types but worth investigating on your side.

Cheers,

Expression in a pivot table help

m4u — Mon, 18 Jan 2010 15:17:50 GMT

Doesn't work

never mind, I give up and handle it on the data load level

thanks for your help

Expression in a pivot table help

Mon, 18 Jan 2010 15:46:35 GMT

Hi,

Based on the data model, I do not think that you will be able to see the display of records on the dates the employee that not worked. The plain simple fact being that the standard hours table does not have any employee name so there is no association for it to show the standard hours against a particular date where employee is a dimension.

What you could do is to create load distinct of employees from your work table and create another table with standard hours for each employee for each date. My thoughts are that this is the only way it will show the standard hours for an employee for dates where he/she has not worked.

Nimish

Expression in a pivot table help

hectorgarcia — Tue, 09 Feb 2010 20:27:10 GMT

I did something to identify hours worked by month to identify employees that did not input hours total or partially in the script

mindate:

load

min(num(Fecha)) as

fechamin

resident

MSP_TimesheetActual;

replace

LET varMinDate = Num(Peek('fechamin', 0, 'mindate'));

LET

varMaxDate = floor( Num(monthend(Today()),0));

//LET varMaxDate = Num(Today());

//Create all fullmpnths in the range from varMinDate to varMaxDate, this is to incude accumulations in months without enrollments

TempCal:

LOAD

$(varMinDate)

+Iterno()-1 AS Num

Date

($(varMinDate)+Iterno()-1) AS

TempDate

AUTOGENERATE

1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate)

;

//Building the master calendar with most date dimensions

table_aux:

load

distinct date(TempDate, 'YYYY-MM-DD') as

Fechavinc_AUX

resident

TempCal order by TempDate

Asc;

Drop

table

TempCal;

// take all periodo and all AUDAFFILIATIONNUMBER

join

load

distinct

recurso1

recurso_AUX

resident

MSP_TimesheetResource

//order by keyusermonth,Fecha

where

not exists(R1, recurso1)

;

auxtable

load

Fechavinc_AUX

AS creationdate

recurso_AUX

recurso

resident

table_aux

order

by recurso_AUX ,

Fechavinc_AUX;

left

join

load

recurso

creationdate

sum

(Horas) as

cantidad

resident

timesheet1

where

flagappr=1

group

by recurso , creationdate

order

by recurso , creationdate

;

//store auxtable into auxtable.qvd;

//drop table table_aux;

horasporusermes:

load

distinct

recurso

left

(creationdate,10) as Fecha

(isnull(cantidad) or cantidad<1,0,cantidad) AS Cantidademp

//numsum (if ( AUDAFFILIATIONNUMBER = PEEK(AUDAFFILIATIONNUMBER) , rangesum ( cantidad , peek (acumactiv1)) , cantidad)) as acumactiv1

rangesum (if ( recurso = previous(recurso) and monthname(creationdate)=previous(monthname(creationdate)) , rangesum ( cantidad , peek (acumactiv1)) , cantidad)) as

acumactiv1

resident

auxtable

order

by recurso , creationdate

;

drop

table

auxtable;

drop

table

table_aux;