topic Re: Month wise count mismatch in QlikView

Month wise count mismatch

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

hi,

I have master calendar showing year, month and date.

If I select 2017 and feb it is showing as below chart:

chart-1

total count must be:117 only but it is showing for 194.

If I select 2017 and feb with particular date means it is showing correct count as given below:

eg:01/02/2017

chart-2

It is showing the correct count for 98.

Can any one help me how to achieve the correct count for monthly wise also..........

regards,

Yoganantha Prakash G.P

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:08:02 GMT

Would you be able to share your app? or at least the expression you are using?

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:10:20 GMT

I have used dimension as Allocation level(Which have all the above three for day wise) thirty count for each employee per month

and

Expression as (Count (distinct Employee))

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:14:38 GMT

I think distinct might be the reason behind the issue... distinct is evaluated based on the dimension. If you have a single employee number falling under two categories, it will be double counted here.

Re: Month wise count mismatch

Anil_Babu_Samineni — Thu, 06 Apr 2017 16:15:23 GMT

May be whole February has that much total in your data set. If you select particular data it may have 98 only. Can you recheck with Text box by using same Expression and selection

Try for Chart-1 expression by using date = {'01/02/2017'}

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:15:55 GMT

how can I separate it can you give any suggestion Sunny......

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:16:06 GMT

For instance

FieldName, Date, EmployeeNumber

Occupied, 02/01/2017, 100

Available, 02/02/2017, 100

When you select 02/01/2017, Employee number 100 will show in only one bucket, but if you select Feb 2017, then he will be within Occupied and Available

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:16:57 GMT

Look at my example below... which category will employee number 100 fall into? Occupied or Available?

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:17:42 GMT

Anil,

day wise means count showing correctly count for feb 1 is 98.

But month wise count means it should be 117 but it is wrong count showing that is my problem.

Re: Month wise count mismatch

Anil_Babu_Samineni — Thu, 06 Apr 2017 16:19:16 GMT

Would you able to share sample please

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:21:09 GMT

It is based on the worked hours of the employee sunny,

If he worked

02/01/2017,100, 8(worked hours) , Occupied

02/02/2017,100,4(worked hours), Available

when we select Feb 2017 he must be Partially Occupied (8+4)/(2(no.of dates)) = 6 as per requirement

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:26:19 GMT

But here 6 is the number of hours and you are not showing number of hours in your chart. You are showing the number of people in each of the group, right? You want to show 3/4th employee in Occupied and 1/4th in Available?

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:26:54 GMT

In the above image employee 8 works in the above days in different hours based on the hours its allocation level is showing for each day. but When I select month and year in master calendar it is counting each number of occupied count and available count for him and displays the count with duplicate.

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:29:32 GMT

I want to show the employee count only in the chart ......

For particular day wise it is counting correctly. As you mentioned the categoires duplicate and its count also duplicate how to restrict it

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:30:56 GMT

That's what I am trying to understand. If you have this data

FieldName, Date, EmployeeNumber

Occupied, 02/01/2017, 100

Available, 02/02/2017, 100

Occupied, 02/02/2017, 100

and you select Feb 2017. What will the chart look like?

Re: Month wise count mismatch

yoganantha321 — Thu, 06 Apr 2017 16:39:53 GMT

The allocation is based on the worked hours. So if I select year and month only means the sum of worked hours in month must be calculated automatically and is allocation level must be freezed example:Occupied.

Then in Occupied bar the count must be 1.

Re: Month wise count mismatch

sunny_talwar — Thu, 06 Apr 2017 16:43:25 GMT

My bad, this is the example I wanted to use

02/01/2017,100, 8(worked hours) , Occupied

02/02/2017,100,4(worked hours), Available

02/02/2017,100,4(worked hours), Occupied

In Feb, Employee number 100 was available for 4 hours and occupied for 12 hours. He would only show up in occupied? why? because he was more occupied then available? (12 vs 4 hours)

If the data looked like this, he would be counted in available now?

02/01/2017,100, 8(worked hours) , Available

02/02/2017,100,4(worked hours), Occupied

02/02/2017,100,4(worked hours), Available

Re: Month wise count mismatch

vinieme12 — Thu, 06 Apr 2017 17:56:37 GMT

Please post a sample application so people here can help you better

Preparing examples for Upload - Reduction and Data Scrambling