topic Re: Question about how Aggr works in App Development

Question about how Aggr works

Anonymous — Thu, 24 May 2018 06:21:43 GMT

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?

Re: Question about how Aggr works

ogautier62 — Thu, 24 May 2018 07:28:58 GMT

Hi,

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

regards

Re: Question about how Aggr works

sunny_talwar — Thu, 24 May 2018 12:10:08 GMT

What if you just use this

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

Re: Question about how Aggr works

bme — Fri, 25 May 2018 17:56:01 GMT

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

Re: Question about how Aggr works

Anonymous — Fri, 25 May 2018 19:21:42 GMT

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