SUM(AGGR) function seems to be ignoring zero values

[maxcsquared]

Hello! I'm creating a scoring system that compares individuals' concentration in different buckets against the global breakdown and then adds up those deviations for each individual. However, I'm having trouble with the SUM(AGGR()) functions. It seems to work correctly except when an individual has a bucket with "zero" count. The scoring formula always works correctly when fully written out as follows:

{<DataFlag = {'Include'}>}
fAbs((count({<Score={'Risk1'}>} total ID) / count(total ID)) - (count({<Score={'Risk1'}>} ID) / count(ID)))
+ fAbs((count({<Score={'Risk2'}>} total ID) / count(total ID)) - (count({<Score={'Risk2'}>} ID) / count(ID)))
+ fAbs((count({<Score={'Risk3'}>} total ID) / count(total ID)) - (count({<Score={'Risk3'}>} ID) / count(ID)))
+ fAbs((count({<Score={'Risk4'}>} total ID) / count(total ID)) - (count({<Score={'Risk4'}>} ID) / count(ID)))
+ fAbs((count({<Score={'Risk5'}>} total ID) / count(total ID)) - (count({<Score={'5'}>} ID) / count(ID)))

The condensed aggregation function provides the exact same result except in cases where one or more buckets has count(ID) = 0. See below for that expression:

{<DataFlag = {'Include'}>}
Sum(
    Aggr(
        fAbs(
            (Count(TOTAL <Score> ID) / Count(TOTAL ID)) // Global ratio
            -
            (Count(TOTAL <IndividualId, Score> ID) / Count(TOTAL <IndividualId> ID)) // Indivudal ratio
        ),
        IndividualId, Score
    )
)

I have tried various IF and ALT statements on parts of the formula that could be returning null values but to no avail. Any help to get the aggregation function working would be greatly appreciated! Thank you.

[M_B]

Can you try the following:

{<DataFlag = {'Include'}>}
Sum(
    fAbs(
        Aggr(
            Count(ID) / Count(TOTAL ID),
            Score
            )
        -
        Aggr(
            Count(ID) / Count(TOTAL ID),
            IndividualId, Score
            )
        )
    )

[maxcsquared]

I tried this formula both as you recommended and with the additional total functions that I had included in the numerator of each part of the formula. Unfortunately it did not work. I'm a little bit confused how the aggregation functions would work in your updated formula. Any other suggestions?

[M_B]

I believe I understand what you are trying to achieve but I was not able to. I tried to type in all the data I see in the screenshot into a load inline but it seems I am still missing some columns and other data.

One thing I did come across is generic load as I was trying to pivot the table.

Maybe someone can provide you with a proper solution.

[Or]

Not sure if I'm reading this correctly, but it seems there's no Score for some of your combinations? Aggr() won't iterate over value sets that don't exist in the data, if memory serves.

[maxcsquared]

Yes, it sounds like you are understanding correctly. The part that's confusing me is that the "deviation" piece I'm adding up with the second part of the equation does exist. If an individual does not have any IDs for a certain risk bucket, the "deviation" would just be the abs(global average - 0). But based on what you said, it doesn't iterate at all for each risk bucket if the count is 0 in the first place, and that most likely explains the issue that I'm having. Is there anyway to force aggr() to run for each one even if the count is 0?

[Or]

Your issue here isn't that the count is 0, it's that there's no combination of ID and Score to iterate over because it doesn't exist in the underlying data. You could stuff your source data so that all combinations of ID and Score exist (with null in the ID field to avoid it being counted), I guess? Or possibly use ValueLoop() instead of Score (I forget if you're allowed to use that in aggr(), but I think so)? It may be possible to do this in other ways, but those are the ones that immediately come to mind.

[maxcsquared]

Thank you very much, I'll give that a try. It makes sense why aggr() wouldn't work in this case.