Aggr + Stdev by Material Group

[sogloqlik]

Hi,

I got the following request from the business:

for each Material Group, how many of the last 6 days had a complaint count greater than the Material Group’s rolling 12-month average + standard deviation.

My current measure is:

Count(
    {<
        [Complaint Open Date] =
        {">=$(vLast6Days)<=$(vMaxComplainDate)"}
    >}
    Aggr(
        If(
            Count(
                Distinct
                {<
                    [Classification 1] = {'Contact Complaint'}
                >}
                [Complaint Number]
            )
            = $(vAvgAndStdevCompGeneral),
            1
        ),
        [Complaint Open Date]
    )
)

The variable vAvgAndStdevCompGeneral is currently:

(
    (
        Aggr(
            Count(
                Distinct
                {<
                    [Classification 1] = {'Contact Complaint'}
                >}
                [Complaint Number]
            ),
            [Material Group Code]
        )
    )
    /
    Count(
        {<
            [Material Group Code] =,
            [Material Group] =
        >}
        Distinct [Complaint Open Date]
    )
)
+
Stdev(
    Aggr(
        Count(
            {<
                [Classification 1] = {'Contact Complaint'},
                COMP_isWorkingDay = {'1'}
            >}
            [Complaint Number]
        ),
        [Complaint Open Date]
    )
)

The measure works when I select one Material Group, but not when Material Group is used as a dimension in a table.

I now also need the average to be based on a rolling 12-month period, while the final count should still evaluate only the last 6 days.

How should I modify the expression so that the calculation works independently for every Material Group in the table?

Thx