Question about how Aggr works

[Anonymous]

I'm trying to count the number of months with at least 1 issue. And then divide that by the total number of months present in the data.

This formula allows me to count the total number of months: count(DISTINCT {<status={'issued'}>}Month). Refer to below left table for raw data and the right table for the calculated value.

This formula allows measure the max number of months in the dataset: Max(Aggr(count(DISTINCT {<status={'issued'}>}Month),stationID)). It returns 12 in this case.

However when I divide the first equation by the second equation in a table, where one of the dimensions is stationID, i dont get the expected output of formula1  / 12. Instead  is either 100% or NULL.

I'm not sure why I'm getting this output, any help would be appreciated! I imagine it must be something do with how the aggregated formula reacts to the stationID dimension?

[ogautier62]

Hi,

if you've got NULL may be try to add NODISTINCT before aggr

regards

[sunny_talwar]

What if you just use this

Count(TOTAL <stationID> DISTINCT {<status={'issued'}>}Month)

[bme · Accepted solution]

The outer aggregation on your function is still calculated using the table dimensions. Since your Aggr is done by stationID and that same field is used as your dimension it's doing a max for each stationID which is why you're getting 100%'s. You need the total modifier to get the max for the whole set.

count(DISTINCT {<status={'issued'}>}Month)/

Max(TOTAL Aggr(count(DISTINCT {<status={'issued'}>}Month),stationID))

[Anonymous]

Thanks Ben for the clear explanation of what I was doing wrong and what is the correct function to use!