Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthewp
Creator III
Creator III

error in set modifier expression for avg expression with clause

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)

1 Solution

Accepted Solutions
sunny_talwar

or this

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

View solution in original post

10 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI try this,

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

May be this

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

sunny_talwar

or this

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

matthewp
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Solved this without set analysis:

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