topic Re: Moving Range Chart with Enabling Trellis Chart in QlikView

Moving Range Chart with Enabling Trellis Chart

Mon, 09 Feb 2015 08:03:14 GMT

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))

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.

jonathandienst — Mon, 09 Feb 2015 08:51:07 GMT

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),

)

, Month, Country))

)

HTH

Jonathan

Tue, 10 Feb 2015 00:51:55 GMT

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?

jonathandienst — Tue, 10 Feb 2015 10:06:59 GMT

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