Moving Range Chart with Enabling Trellis Chart

[#1]

Hello, I'm trying to create a Moving Range Chart with Enabling Trellis Chart.

If I calculate MRBAR without additional dimension for Trellis Chart, it is working fine.

      where MRBAR = Avg({<Month=>} Total  Aggr(fabs(Above(sum(Data))-sum(Data)), Month))

Month	X	XBAR	MR	MRBAR
1406	191.1	208.3	-	9.0
1407	197.7	208.3	6.6	9.0
1408	203.3	208.3	5.6	9.0
1409	207.0	208.3	3.7	9.0
1410	217.1	208.3	10.1	9.0
1411	218.5	208.3	1.4	9.0
1412	204.9	208.3	13.6	9.0
1501	226.8	208.3	21.9	9.0

But if I add the dimension (country) for Trellis Chart, MRBAR is calculated wrongly like below.

  where MRBAR = Avg({<Month=>} Total <Country> Aggr(fabs(Above(sum(Data))-sum(Data)), Month, Country))

I think MRBAR should be the average of MR in below table, but the result is different from what I expected.

I've attached the qlikview document I've created for this test.

Please, kindly help to check and advise what went wrong.

Country	Month	X	XBAR	MR	MRBAR
A	1406	61.8	62.7	-	-
A	1407	57.5	62.7	4.241949023	-
A	1408	65.5	62.7	7.925399452	-
A	1409	56.2	62.7	9.230154472	-
A	1410	67.3	62.7	11.06479128	-
A	1411	65.8	62.7	1.492584338	-
A	1412	61.9	62.7	3.897969474	-
A	1501	65.9	62.7	3.952960342	-
B	1406	60.5	63.2	-	4.615732832
B	1407	58.5	63.2	2.017739632	4.615732832
B	1408	52.9	63.2	5.55935653	4.615732832
B	1409	61.0	63.2	8.074766386	4.615732832
B	1410	64.6	63.2	3.555229698	4.615732832
B	1411	68.0	63.2	3.471891879	4.615732832
B	1412	70.5	63.2	2.421747692	4.615732832
B	1501	69.8	63.2	0.687787389	4.615732832
C	1406	30.1	30.5	-	32.6980821
C	1407	34.1	30.5	4.018008254	32.6980821
C	1408	32.6	30.5	1.533465315	32.6980821
C	1409	40.1	30.5	7.587028331	32.6980821
C	1410	24.5	30.5	15.63069592	32.6980821
C	1411	29.9	30.5	5.387726237	32.6980821
C	1412	23.8	30.5	6.09695738	32.6980821
C	1501	29.1	30.5	5.26075232	32.6980821
D	1406	38.7	51.8	-	21.29796788
D	1407	47.6	51.8	8.889813238	21.29796788
D	1408	52.3	51.8	4.764452952	21.29796788
D	1409	49.6	51.8	2.731628221	21.29796788
D	1410	60.7	51.8	11.07863112	21.29796788
D	1411	54.7	51.8	5.952107233	21.29796788
D	1412	48.8	51.8	5.988287185	21.29796788
D	1501	62.1	51.8	13.39396333	21.29796788

[jonathandienst]

Hi

You need a total in the Above, and you need a condition to handle the change from one country to the next. Perhaps something like:

= Avg({<Month=>} Total <Country>

  Aggr(Fabs(

    If(Country = Above(TOTAL Country),

      Above(TOTAL Sum(Data))-Sum(Data),

     0

    )

, Month, Country))

)

HTH

Jonathan

[#3]

Hello Jonathan,

Thank you for your kind advice.

I've changed the expression for MRBAR like below, but it didn't work.

• MRBAR = Avg({<Month=>} Total <Country> Aggr(fabs(if(Country = Above(Total Country), Above(Total sum(Data))-sum(Data),0)), Month, Country))

Somehow, all the values are now appeared as '0'.

Could you please kindly check the qvw file I've posted above and help if there is any way to plot the Trellis enabled Control Charts using MRBAR?

Country	Month	X	XBAR	MR	MRBAR
A	1406	61.8	62.7	-	0
A	1407	57.5	62.7	4.241949023	0
A	1408	65.5	62.7	7.925399452	0
A	1409	56.2	62.7	9.230154472	0
A	1410	67.3	62.7	11.06479128	0
A	1411	65.8	62.7	1.492584338	0
A	1412	61.9	62.7	3.897969474	0
A	1501	65.9	62.7	3.952960342	0
B	1406	60.5	63.2	-	0
B	1407	58.5	63.2	2.017739632	0
B	1408	52.9	63.2	5.55935653	0
B	1409	61.0	63.2	8.074766386	0
B	1410	64.6	63.2	3.555229698	0
B	1411	68.0	63.2	3.471891879	0
B	1412	70.5	63.2	2.421747692	0
B	1501	69.8	63.2	0.687787389	0
C	1406	30.1	30.5	-	0
C	1407	34.1	30.5	4.018008254	0
C	1408	32.6	30.5	1.533465315	0
C	1409	40.1	30.5	7.587028331	0
C	1410	24.5	30.5	15.63069592	0
C	1411	29.9	30.5	5.387726237	0
C	1412	23.8	30.5	6.09695738	0
C	1501	29.1	30.5	5.26075232	0
D	1406	38.7	51.8	-	0
D	1407	47.6	51.8	8.889813238	0
D	1408	52.3	51.8	4.764452952	0
D	1409	49.6	51.8	2.731628221	0
D	1410	60.7	51.8	11.07863112	0
D	1411	54.7	51.8	5.952107233	0
D	1412	48.8	51.8	5.988287185	0
D	1501	62.1	51.8	13.39396333	0

[jonathandienst]

Hi

These are the correct expressions:

XBAR:  =Avg({< Month=>} TOTAL Aggr(Sum(Data), Country, Month))

MRBAR: =Avg(TOTAL <Country> Aggr(Fabs(Above(Sum(Data)) - Sum(Data)), Country, Month))

This will compute the same XBAR for all countries. Is this what you require? Otherwise XBAR is

XBAR (by country):  =Avg(TOTAL <Country> Aggr(Sum(Data), Country, Month))

Updated chart and table on Sheet1 of the attached.

HTH

Jonathan