topic Re: Aggr syntax check in QlikView

Aggr syntax check

samuel_brierley — Wed, 09 Sep 2015 09:28:08 GMT

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.

Re: Aggr syntax check

Wed, 09 Sep 2015 09:30:31 GMT

try this

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

Thanks

Manju

Re: Aggr syntax check

sunny_talwar — Wed, 09 Sep 2015 09:31:26 GMT

Try this may be:

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

Re: Aggr syntax check

samuel_brierley — Wed, 09 Sep 2015 09:33:53 GMT

Sorry guys no closer

Re: Aggr syntax check

sunny_talwar — Wed, 09 Sep 2015 09:35:25 GMT

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

Re: Aggr syntax check

jonathandienst — Wed, 09 Sep 2015 09:38:00 GMT

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?

Re: Aggr syntax check

samuel_brierley — Wed, 09 Sep 2015 09:38:55 GMT

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

Re: Aggr syntax check

samuel_brierley — Wed, 09 Sep 2015 09:41:34 GMT

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

Re: Aggr syntax check

jonathandienst — Wed, 09 Sep 2015 09:56:32 GMT

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

Re: Aggr syntax check

samuel_brierley — Wed, 09 Sep 2015 09:59:10 GMT

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

Re: Aggr syntax check

sunny_talwar — Wed, 09 Sep 2015 09:59:25 GMT

The 'dimensions' in an Aggr() cannot be calculated dimensions, only field names are allowed here.

Learned something new today . Thank you jonathan