Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anagharao
Creator II
Creator II

pivot table - expression

Hi Everyone,

I have data that looks something like this

                   Compliant          In office          No time booked

Employee

EMP1                                                               1

EMP2                                     1

EMP3            1

.

.

The dimension is employee and Aggr(FirstSortedValue([COMPLIANCE REASON] , -[COMPLIANCE PRIORITY]), EMP_ID)

and the expression COUNT(DISTINCT EMP_ID)


This works fine. However there is time booked by these employees that i want to capture. SUM([TIME]) leads to incorrect values. It adds up all time regardless of the compliant values.

what i have

                   Compliant          In office          No time booked

Employee

EMP1                                                               320

EMP2                                     320

EMP3            320

what i need

                   Compliant          In office          No time booked

Employee

EMP1                                                               7

EMP2                                     10

EMP3            320

The above values are because in the 320 hrs EMP1 and EMP2 have been non compliant for 7,10 hrs only. Any ideas ?

13 Replies
sunny_talwar

How are you getting 7 for Emp1? and 10 for Emp2?

Emp1

Capture.PNG

Emp2

Capture.PNG

anagharao
Creator II
Creator II
Author

7 - Emp1 as the time to be booked is 7 on week 2 in which he has not been complaint. and no values for the the others since he has been non complaint.

10 for emp2 as time booked for "in office" is 10. the no time booked and complaint times need to be ignored here.

sunny_talwar

I still don't think I understand where you are coming up with those numbers.... can you highlight the numbers in an image to show where are getting them from?

anagharao
Creator II
Creator II
Author

EMP_ID      WEEKID     PROJ_ID     [TIME TO BE BOOKED]      [TIME BOOKED]     COMPLAINCE_ID

EMP1          1                           1             7                                             7                             1

EMP1          2                           1             7                                             -                              3

EMP2          1                           3             10                                             -                              3

EMP2          1                           6             -                                             10                               2  

EMP2          2                           3              10                                           10                             1

EMP3          1                          4               6                                             6                              1

EMP3          1                          5               2                                             2                              1

EMP3          2                          4               6                                             6                              1

EMP3          2                          5               2                                             2                              1


I've highlighted the above two rows that i 've used to derive my non compliance times