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]

Strange.. what happens if you make a text object and put the following statement: Count(Distinct {$< Year = {2019}>} DAY). Does this also return 31?

Can you maybe add a qvf?

Jordy

Climber

[Lech_Miszkiewicz]

Hi Edyta,

If what you are saying is right the formula should work, however looking on your results it looks like you dont have distinct number for each day in year. It simply looks like you are counting distinct days of months instead of days of year. As this is data issue without sample it will be hard to answer your question. 

[Edyta]

that's right. Apologies, I have not noticed it. I have number of week of of the year and number of day within month.

[JordyWegman · Accepted solution]

Then try this formula again:

Sum(Aggr(count(distinct [DAY],Month))) 

Jordy

Climber 

[Edyta]

Jordy almost correct, there should be closing parantheses after [DAY] and the last should be removed.

Guys, thank you very much for your help.

[JordyWegman]

Good, did this solve your problem? Then please mark the post as solved!

Jordy

Climber