topic pivot table - expression in QlikView

pivot table - expression

anagharao — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: pivot table - expression

sunny_talwar — Tue, 26 Dec 2017 13:26:18 GMT

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

Re: pivot table - expression

pradosh_thakur — Tue, 26 Dec 2017 13:28:07 GMT

a sample please.

Re: pivot table - expression

anagharao — Tue, 26 Dec 2017 13:33:43 GMT

apologies.. i cannot upload any files.

Re: pivot table - expression

sunny_talwar — Tue, 26 Dec 2017 13:34:49 GMT

Can you at least provide mocked up data?

Re: pivot table - expression

Anonymous — Tue, 26 Dec 2017 13:35:46 GMT

Are you joing multiple tables to create this table?

Re: pivot table - expression

anagharao — Tue, 26 Dec 2017 13:48:26 GMT

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

];

Re: pivot table - expression

sunny_talwar — Tue, 26 Dec 2017 14:52:57 GMT

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?

Re: pivot table - expression

anagharao — Thu, 28 Dec 2017 09:21:43 GMT

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

Re: pivot table - expression

anagharao — Thu, 28 Dec 2017 09:22:35 GMT

yes, it derives data from fact and dimensions table

Re: pivot table - expression

sunny_talwar — Thu, 28 Dec 2017 12:23:01 GMT

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

Emp1

Emp2

Re: pivot table - expression

anagharao — Thu, 18 Jan 2018 10:02:13 GMT

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.

Re: pivot table - expression

sunny_talwar — Thu, 18 Jan 2018 11:02:18 GMT

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?

Re: pivot table - expression

anagharao — Thu, 18 Jan 2018 11:54:40 GMT

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