Aggr syntax check

[samuel_brierley]

Can someone spot where im going wrong?

aggr(avg(count({$< MainCarriagewayDataFilteringOutput.site1.v = {'<50'}>} Timestamp)),weekday(Date))

what its supposed to do is produce the average number of minutes (theres one record a min) for each day.

[#2]

Hi

try this

avg(aggr(count({$< MainCarriagewayDataFilteringOutput.site1.v = {'<50'}>} Timestamp)),weekday(Date))

Thanks

Manju

[sunny_talwar]

Try this may be:

=Avg(Aggr(Count({$< MainCarriagewayDataFilteringOutput.site1.v = {'<50'}>} Timestamp),WeekDay(Date)))

[samuel_brierley]

Sorry guys no closer

[sunny_talwar]

Would you be able to share a sample with expected output?

[jonathandienst]

How and where are you using this expression? If its a table expression, what are the dimensions in the table? Is MainCarriagewayDataFilteringOutput.site1.v a field name (remember that field names are case sensitive)? Are there any rows with the value 50? Are TimeStamp and Date also field names?

[samuel_brierley]

Yeah no problem,

if you look in the project plan tab theres a table that reads minutes below 50, should find its self in there.

thanks

[samuel_brierley]

Im using the expression in this table, currently the table has the expression

count({$< MainCarriagewayDataFilteringOutput.site1.v = {'<50'}>} Timestamp)

which will count all of the minutes for e.g all Mondays and i want to show the average count for Mondays hence the aggr.

the table shows that the dimensions and the field names are correct and that there are in deed values below 50

[jonathandienst · Accepted solution]

OK, I see the problem. The 'dimensions' in an Aggr() cannot be calculated dimensions, only field names are allowed here. If you add the WeekDay(Date) to your calendar, and use that as the chart dimension, then Sunny's Aggr should work (modified to the new dimension "Day"):

     LOAD ...

          Date,

          WeekDay(Date) As Day,

          ...

Use Day as the chart dimension, and then use

     =Avg(Aggr(Count({$< MainCarriagewayDataFilteringOutput.site1.v = {'<50'}>} Timestamp),Day))

[samuel_brierley]

Thanks Jonathan, I wasn't aware expressions couldn't be used there.

I was trying desperately to avoid making a change to the script (the whole script is the result of VBA code in a spreadsheet)

if it has to be done might as well get on with it.

thanks dude