topic Re: Help in Expression in QlikView

Help in Expression

ali_hijazi — Thu, 14 May 2020 11:11:34 GMT

Hello got a table that contains the year, quarter, month, and the employee as well as a field called employee count which is 1 next to each employee

I need to create a pivot table in which I have the year, quarter, and month as dimensions

as a measure I want to count the number of employees

per month I count the employees so if the employee is available in 3 months then in each of these months I count the employee but per year I should count the employee only once
so depending on whether the table is collapsed or fully expanded I should get different counts
example JOE should be counted per month but per year or quarter I shouldn't get 3 I should get 1

check attached sample application please and help on how to fix this issue

Re: Help in Expression

tresB — Thu, 14 May 2020 11:22:40 GMT

Did you try just :

Count(Distinct EMPLOYEE)

Re: Help in Expression

ali_hijazi — Thu, 14 May 2020 12:00:21 GMT

works fine if the pivot table is expanded at level of month but if collapsed the count distinct will result in 3 for one employee

Re: Help in Expression

tresB — Thu, 14 May 2020 12:27:21 GMT

Am I missing something? Check the screenshots below (last column).

Re: Help in Expression

ali_hijazi — Thu, 14 May 2020 14:32:11 GMT

YES I forgot to tell you

that when I collapse to the level of the year I should get 2 instead of 3 because at the level of the year I need to read the total count of the last Quarter
I updated the QVW file

Re: Help in Expression

ali_hijazi — Fri, 15 May 2020 02:34:24 GMT

Hello @tresB
Maybe what I wrote in the following thread elaborates more what I want
Getting value of max month in set Analysis

Re: Help in Expression

tresB — Fri, 15 May 2020 06:29:51 GMT

Unfortunately I can't access the link

Re: Help in Expression

ali_hijazi — Fri, 15 May 2020 07:01:04 GMT

I already mentioned you in that Thread