Average for Count Data - SPC Chart

[MS_UK]

Hi All,

I am trying to do SPC Chart and I stuck with Average. I have counted repairs per week and I am trying to calculate total average of repairs per week. 

Aa a dimmension I have Week Number

Expression 1  = Count(RepairsID)

Expression 2 =Avg(Aggr(Count(RepairsID), WeekNumberSUNSAT)), the same effect when I type =count(RepairID)/ count( distinct WeekNumberSUNSAT)

Results in table below ( I extracted only 20 weeks for one of the year). 

In total for 4 years I have 8155 repairs, so average should be 39.2067 repairs per week, which obviously I didn't get with my expression. 

In script under RepairID i have below statement:

AutoNumber(Repair&Date&Case_No) as RepairID

I tried to replicate calculations from:

community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

community.qlik.com/t5/New-to-QlikView/Average-Count/td-p/1281394

help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Stati...

Could you please advise?

BR

Michael

WeekNumberSUNSAT	Repair_Per_Week	Average
1	154	154.00
2	144	144.00
3	98	98.00
4	139	139.00
5	110	110.00
6	99	99.00
7	101	101.00
8	119	119.00
9	104	104.00
10	127	127.00
11	170	170.00
12	179	179.00
13	225	225.00
14	199	199.00
15	233	233.00
16	180	180.00
17	177	177.00
18	207	207.00
19	200	200.00
20	203	203.00

[Arthur_Fong]

Do you have a sample app?

[MS_UK · Accepted solution]

Hi All,

Thanks for your help,

I found solution, Expression should be as below:

=Avg(TOTAL Aggr(Count(RepairsID), WeekNumberSUNSAT))

Best Regards

Michal