pivot table - expression

[anagharao]

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 ?

[sunny_talwar]

Would it be possible to share a sample to look at the issue?

[pradosh_thakur]

a sample please.

[anagharao]

apologies.. i cannot upload any files.

[sunny_talwar]

Can you at least provide mocked up data?

[Anonymous]

Are you joing multiple tables to create this table?

[anagharao]

hope this helps.

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

Compliance table :

LOAD * INLINE

[

COMPLIANCE_ID, COMPLIANCE, COMPLIANCE REASON, COMPLIANCE PRIORITY

1, 1, COMPLIANT, 1

2, 0, IN OFFICE, 2

3, 0,  NO TIME BOOKED, 3

];

[sunny_talwar]

In the above sample, what would Sum(TIME) be? Sum([TIME TO BE BOOKED]) or Sum([TIME BOOKED])? Also, what would the correct output for the expression we have to use?

[anagharao]

Time is minimum of the two columns "Time to be booked" and "time booked"

the correct output for the above sample data would be :

                   Compliant          In office          No time booked

Employee

EMP1                                                               7

EMP2                                     10

EMP3            16

[anagharao]

yes, it derives data from fact and dimensions table