Cannot get daily production in Qlik Sense

[Edyta]

Hi guys,

I am trying to obtain simple pivot table in Qlik Sense. Everything is fine but I cannot get daily production in items.

Below is piece of raw data:

WEEK	DAY	MONTH	YEAR	NUMBER OF ITEMS	ITEMS REJECTED
1	2	1	2019	2 504	12
1	2	1	2019	3 758	54
1	3	1	2019	4 522	32
1	3	1	2019	5 219	45
1	3	1	2019	4 420	5
1	4	1	2019	31 292	62
1	4	1	2019	34 184	54
1	4	1	2019	30 573	47

Below is the result is the pivot table - the last column AvgDailyItemsProduced is wrongly calculated by Qlik.

I also present how I get CountProductionDays and ItemsProduced:

CountProductionDays: count(distinct [DAY])

ItemsProduced: sum(NUMBER OF ITEMS)-sum(ITEMS REJECTED)

I also tried to get AvgDailyItemsProduced which is the sum of ItemsProduced/CountProductionDays:

sum(NUMBER OF ITEMS)-sum(ITEMS REJECTED)/count(distinct [DAY]) and I obtain wrong value

	CountProductionDays	ItemsProduced	AvgDailyItemsProduced
2019
1	22	5 156 325	=ItemsProduced/CountProductionDays
2	23	6 235 248	=ItemsProduced/CountProductionDays
3	24	5 902 647	=ItemsProduced/CountProductionDays
4	26	5 455 658	=ItemsProduced/CountProductionDays

Could you help me with the issue?

[JordyWegman]

Hi Edyta,

If this is the way you calculated it, it's basic maths that goes wrong here. The * will always be calculated first before the - .

Try the following calculation with the extra brackets around the sums:

(sum(NUMBER OF ITEMS)-sum(ITEMS REJECTED)) /count(distinct [DAY]) 

 Jordy

Climber

[Edyta]

I got it properly per month, but it counts wrongly production for the year of 2019.

Any ideas?

[Edyta]

The value of daily production for the year should be at similar level as the monthly production daily.

I got yearly prodution daily at level 859 734

for January I got 240 895

for February 230 452

[JordyWegman]

But for a year you can't use the distinct amount of days. Try this formula. I don't know what your 'Month' name really is, but fill in your Month column name. 

(sum(NUMBER OF ITEMS)-sum(ITEMS REJECTED)) / Sum(Aggr(count(distinct [DAY],Month))) 

Jordy

Climber

[Edyta]

The option you have provided does not work.

The column DAY of my data contains applicable number of day of year, so in fact each day has different number, thus the formula should count distinct number of production days for particular month and year.

[JordyWegman]

What is the number that you would expect? Can you give also the numerator and denominator? For example, one table with the values per month and one for a year, maybe in Excel?

Jordy

Climber

[Edyta]

Some ideas came to my mind. CountProductionDays are properly counted per month but wrongly calculated per year.

I need to get the sum of CountProductionDays of each month for the year of 2019.

What I am getting now
	CountProductionDays	ItemsProduced	AvgDailyItemsProduced
2019	31	21 856 054	705 034,00
1	26	5 256 545	202 174,81
2	22	6 545 669	297 530,41
3	25	5 487 855	219 514,20
4	22	4 565 985	207 544,77
The solution I need to get
	CountProductionDays	ItemsProduced	AvgDailyItemsProduced
2019	95	21 856 054	230 063,73
1	26	5 256 545	202 174,81
2	22	6 545 669	297 530,41
3	25	5 487 855	219 514,20
4	22	4 565 985	207 544,77

With this formula count( distinct[DAY]) I got wrong number of production days for the year.

Any ideas how to solve it?

[JordyWegman]

That 31 is strange, because it looks like you have duplicates and its only counting the distincts and that is 31 because the max in a month is 31 days..

If you want the sum, you can go the calculation, check the Totals function and set this to Sum. This will sum all the monthly values.

Jordy
Climber

[Edyta]

sum is not the solution.

I use 'count (distinct DAY)' because I need to obtain all distinct days, each day has its seperate number, thus I have no idea why CountProductionDays shows incorrect value for the year of 2019.

Regards

Edyta