AVG calculated field

Question & Answer
,
1

Hi, I am trying to create a calculated field that takes a decimal type measure as the first argument, and a dimension type string as the second group-by argument but i am still encountering the following error:

“Function ‘AVG’ should have 1 argument(s) instead of 2 argument(s)”

my syntax is as follows:

avg({pctGrantFundingDirectService},[{submissionPeriodId}])

2

Hi @jtroxel
try to use

instead.
Seems the AVG doesnt like you 2 argument even it is shown in the documentation.

BR

3

@ErikG Thanks and im trying that now but still getting stuck. What i am trying to do is use a KPI visual to compare “grantFundingObligated” of a specific reporting period selected by a user (submissionPeriodName) with the overall average of this measure by that reporting period. I have set up the calculated field as follows:

avgOver Funding Obligated
avgOver(sum({grantFundingObligated}),[submissionPeriodName])

The KPI visual has field wells for value, target value and trend group. The way i understand it, i need to now put “grantFundingObligated” in the value field well, “avgOver Funding Obligated” in the target value field well, and “submissionPeriodName” in the trend group field well. However, when i try to populate all three of these field wells, in any order, the tooltip displays “cant add measures right now” when i try to add a measure to the last field well, whichever it may be.

How can i use the KPI the way i am describing to show an observed value within a given reporting period against its overall average?

4

Hi @jtroxel
you calulation is working?

It looks like you can use the KPI as trend or target but not both.

grafik

BR

5

Thanks @ErikG
the calculation does appear to work since it does not throw an error when i save it. in the data set preview window, the values do show as “Unavailable”, so not sure how to interpret that.

I have also tried calculating the average the old fashioned way :
sum({grantFundingObligated},[submissionPeriodName])/count({grantFundingObligated},[submissionPeriodName])

but this returns me to my original frustration with being told this function only accepts 1 argument when the documentation indicates otherwise.

my essential question remains, how can i calculate the average of a field grouped by date, so that i can then use the KPI visual to compare a single value on a given date with the average of all values that fall within that same date?

6

@ErikG Ok, I believe i have solved this after finding a clue in another thread. I didn’t know there was such an important distinction between creating a calculated field from within the analysis vs. within the dataset, but i am able to use my original calculations from above without error and the KPI appears to be displaying the correct information now. thank you for your help!

I also needed the overall average to ignore whatever filters were applied within the analysis, and my final working calculation is:

avgOver(grantFundingObligated,[submissionPeriodName], PRE_FILTER)

1 Like
7

Hi @jtroxel , can you show o tell me the link where you found the clue for you solution? please

8

@reyesnes sorry, i am unable to recall it (i believe i saw it in stack overflow) but the clue was simply in spotting the use of “PRE_FILTER” there, which is what got me headed down the right path.