Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner
Partner

Help in Expression

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

I can walk on water when it freezes
7 Replies
tresesco
MVP
MVP

Did you try just :

Count(Distinct EMPLOYEE)

?

ali_hijazi
Partner
Partner
Author

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

I can walk on water when it freezes
tresesco
MVP
MVP

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

1.PNG2.PNG

ali_hijazi
Partner
Partner
Author

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

Untitled.png

I can walk on water when it freezes
ali_hijazi
Partner
Partner
Author

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

I can walk on water when it freezes
tresesco
MVP
MVP

Unfortunately I can't access the link

ali_hijazi
Partner
Partner
Author

I already mentioned you in that Thread

I can walk on water when it freezes