error in set modifier expression for avg expression with clause

[matthewp]

My expression will not work and i am getting the error "error in set modifier expression", it is only a simple expression but i cant understand why it wont work.

avg({<NOT ISNULL([table.ApproveDate])>} table.days)

[kaushiknsolanki]

HI try this,

=avg({<Product -= {"$(=isnull(table.ApproveDate))"}>}tables.days)

Regards,

Kaushik Solanki

[sunny_talwar]

May be this

Avg({<[table.ApproveDate] = {'*'}>} table.days)

[sunny_talwar · Accepted solution]

or this

Avg(If(Len(Trim([table.ApproveDate])) > 0,  table.days))

[matthewp]

This works perfect!

Side question: how can i do the opposite to Avg({<[table.ApproveDate] = {'*'}>} table.days) as in NULL rather than NOT NULL

[sunny_talwar]

This becomes a slightly complicated because of the fact that you are looking for NULL. To do this, I would use a uniqueIdentifier field like this

Avg({<UniqueIdentifier = {"=Len(Trim([table.ApproveDate])) = 0">} table.days)

[matthewp]

What is UniqueIdentifier ?

Also I am getting the error "Error in set modifier ad hoc element list"

[sunny_talwar]

UniqueIdentifier is a placeholder for a field which Uniquely identify each of the row of your table. T

[matthewp]

Ok so using:

Avg({<table.IDNumber= {"=Len(Trim([table.ApproveDate])) = 0">} table.days)

I am still getting the error "Error in set modifier ad hoc element list" even though nothing is underlined in red

[matthewp]

Solved this without set analysis:

=SUM(if(IsNull(table.ApproveDate), table.days))/COUNT(if(IsNull(table.ApproveDate), table.days))